使用SqlConnection对象来读取数据库架构信息示例（包含数据库列表、表名列表、表的字段等信息列表）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

<br>using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
 
namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
 
            using (System.Data.SqlClient.SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Ctrl.Js;Integrated Security=True;"))
            {
                conn.Open();
 
                // Get the Meta Data for Supported Schema Collections
                DataTable metaDataTable = conn.GetSchema("MetaDataCollections");
 
                Console.WriteLine("Meta Data for Supported Schema Collections:");
                ShowDataTable(metaDataTable, 25);
                Console.WriteLine();
 
                // Get the schema information of Databases in your instance
                DataTable databasesSchemaTable = conn.GetSchema("Databases");
 
                Console.WriteLine("Schema Information of Databases:");
                ShowDataTable(databasesSchemaTable, 25);
                Console.WriteLine();
 
                // First, get schema information of all the tables in current database;
                DataTable allTablesSchemaTable = conn.GetSchema("Tables");
 
                Console.WriteLine("Schema Information of All Tables:");
                ShowDataTable(allTablesSchemaTable, 20);
                Console.WriteLine();
 
                // You can specify the Catalog, Schema, Table Name, Table Type to get
                // the specified table(s).
                // You can use four restrictions for Table, so you should create a 4 members array.
                String[] tableRestrictions = new String[4];
 
                // For the array, 0-member represents Catalog; 1-member represents Schema;
                // 2-member represents Table Name; 3-member represents Table Type.
                // Now we specify the Table Name of the table what we want to get schema information.
                tableRestrictions[2] = "Course";
 
                DataTable courseTableSchemaTable = conn.GetSchema("Tables", tableRestrictions);
 
                Console.WriteLine("Schema Information of Course Tables:");
                ShowDataTable(courseTableSchemaTable, 20);
                Console.WriteLine();
 
                // First, get schema information of all the columns in current database.
                DataTable allColumnsSchemaTable = conn.GetSchema("Columns");
 
                Console.WriteLine("Schema Information of All Columns:");
                ShowColumns(allColumnsSchemaTable);
                Console.WriteLine();
 
                // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
                // You can use four restrictions for Column, so you should create a 4 members array.
                String[] columnRestrictions = new String[4];
 
                // For the array, 0-member represents Catalog; 1-member represents Schema;
                // 2-member represents Table Name; 3-member represents Column Name.
                // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
                columnRestrictions[2] = "Course";
                columnRestrictions[3] = "DepartmentID";
 
                DataTable departmentIDSchemaTable = conn.GetSchema("Columns", columnRestrictions);
 
                Console.WriteLine("Schema Information of DepartmentID Column in Course Table:");
                ShowColumns(departmentIDSchemaTable);
                Console.WriteLine();
 
                // First, get schema information of all the IndexColumns in current database
                DataTable allIndexColumnsSchemaTable = conn.GetSchema("IndexColumns");
 
                Console.WriteLine("Schema Information of All IndexColumns:");
                ShowIndexColumns(allIndexColumnsSchemaTable);
                Console.WriteLine();
 
                // You can specify the Catalog, Schema, Table Name, Constraint Name, Column Name to
                // get the specified column(s).
                // You can use five restrictions for Column, so you should create a 5 members array.
                String[] indexColumnsRestrictions = new String[5];
 
                // For the array, 0-member represents Catalog; 1-member represents Schema;
                // 2-member represents Table Name; 3-member represents Constraint Name;4-member represents Column Name.
                // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
                indexColumnsRestrictions[2] = "Course";
                indexColumnsRestrictions[4] = "CourseID";
 
                DataTable courseIdIndexSchemaTable = conn.GetSchema("IndexColumns", indexColumnsRestrictions);
 
                Console.WriteLine("Index Schema Information of CourseID Column in Course Table:");
                ShowIndexColumns(courseIdIndexSchemaTable);
                Console.WriteLine();
            }
 
            Console.WriteLine("Please press any key to exit...");
            Console.ReadKey();
        }
 
        private static void ShowDataTable(DataTable table, Int32 length)
        {
            foreach (DataColumn col in table.Columns)
            {
                Console.Write("{0,-" + length + "}", col.ColumnName);
            }
            Console.WriteLine();
 
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {
                    if (col.DataType.Equals(typeof(DateTime)))
                        Console.Write("{0,-" + length + ":d}", row[col]);
                    else if (col.DataType.Equals(typeof(Decimal)))
                        Console.Write("{0,-" + length + ":C}", row[col]);
                    else
                        Console.Write("{0,-" + length + "}", row[col]);
                }
                Console.WriteLine();
            }
        }
 
        private static void ShowDataTable(DataTable table)
        {
            ShowDataTable(table, 14);
        }
 
        private static void ShowColumns(DataTable columnsTable)
        {
            var selectedRows = from info in columnsTable.AsEnumerable()
                               select new
                               {
                                   TableCatalog = info["TABLE_CATALOG"],
                                   TableSchema = info["TABLE_SCHEMA"],
                                   TableName = info["TABLE_NAME"],
                                   ColumnName = info["COLUMN_NAME"],
                                   DataType = info["DATA_TYPE"]
                               };
 
            Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", "TableCatalog", "TABLE_SCHEMA",
                "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE");
            foreach (var row in selectedRows)
            {
                Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", row.TableCatalog,
                    row.TableSchema, row.TableName, row.ColumnName, row.DataType);
            }
        }
 
        private static void ShowIndexColumns(DataTable indexColumnsTable)
        {
            var selectedRows = from info in indexColumnsTable.AsEnumerable()
                               select new
                               {
                                   TableSchema = info["table_schema"],
                                   TableName = info["table_name"],
                                   ColumnName = info["column_name"],
                                   ConstraintSchema = info["constraint_schema"],
                                   ConstraintName = info["constraint_name"],
                                   KeyType = info["KeyType"]
                               };
 
            Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", "table_schema", "table_name", "column_name", "constraint_schema", "constraint_name", "KeyType");
            foreach (var row in selectedRows)
            {
                Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", row.TableSchema,
                    row.TableName, row.ColumnName, row.ConstraintSchema, row.ConstraintName, row.KeyType);
            }
        }
    }
}