C# 获取表的架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/173834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting the schema for a table
提问by Omar Kooheji
Given an SQLConnection object how can you get a schema for a single table?
给定一个 SQLConnection 对象，如何获得单个表的架构？
I was trying this the other day and I seemed to be able to get the schema from a DataSet which I'd gotten from running a query, but all the schema info I could get from the connection seemed to be related to what tables were available and not the actual details on the tables.
前几天我正在尝试这个，我似乎能够从我通过运行查询获得的数据集中获取模式，但是我可以从连接中获得的所有模式信息似乎都与可用的表有关而不是表上的实际细节。
I'm sure there is a simple way to do this.
我相信有一种简单的方法可以做到这一点。
采纳答案by Omar Kooheji
I think accessing the schema from the query (via GetSchemaTable) is the only way to do it. You can run a query which returns no rows (select * from table where 1=2) if the schema is all you're interested in.
我认为从查询（通过 GetSchemaTable）访问架构是唯一的方法。如果您只对架构感兴趣，则可以运行不返回任何行的查询（从表中选择 *，其中 1=2）。
You should use the KeyInfo CommandBehaviour to execute the source query, as otherwise not all the information returned is guaranteed to be accurate
您应该使用 KeyInfo CommandBehaviour 来执行源查询，否则不能保证返回的所有信息都是准确的
Command.ExecuteReader(CommandBehavior.KeyInfo)
回答by Mitch Wheat
This code will do what you want (obviously change the table name, server name etc):
此代码将执行您想要的操作（显然更改表名、服务器名等）：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
string query = "SELECT * FROM t where 1=0";
string connectionString = "initial catalog=test;data source=localhost;Trusted_Connection=Yes";
DataTable tblSchema;
using (SqlConnection cnn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cnn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
tblSchema = rdr.GetSchemaTable();
}
cnn.Close();
}
}
int numColumns = tblSchema.Columns.Count;
foreach (DataRow dr in tblSchema.Rows)
{
Console.WriteLine("{0}: {1}", dr["ColumnName"], dr["DataType"]);
}
Console.ReadLine();
}
}
}
回答by KristoferA
SQL Server - query the catalog views... sysobjects, syscolumns etc if SQL 2000 or earlier... sys.objects, sys.columns etc if SQL 2005 or higher. (although the older views are still available it is advisable to use the newer ones)
SQL Server - 查询目录视图... sysobjects、syscolumns 等（如果 SQL 2000 或更早版本）... sys.objects、sys.columns 等（如果 SQL 2005 或更高版本）。（虽然旧视图仍然可用，但建议使用较新的视图）
Complete reference here: http://msdn.microsoft.com/en-us/library/ms189783.aspx
完整参考：http: //msdn.microsoft.com/en-us/library/ms189783.aspx
Example:
例子：
select so.name, sc.*
from sys.objects as so
inner join sys.columns as sc on sc.object_id = so.object_id
where so.name='some_table'
