blocks|key|3467841|text|有关表和列的一般信息可在以下表格中找到：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3467842|select+*+from+INFORMATION_SCHEMA.TABLES
select+*+from+INFORMATION_SCHEMA.COLUMNS|code-block|syntax|javascript|3467843|表描述是一个扩展属性，您可以从sys.extended_properties中查询：|3467844|select+
++++TableName+=+tbl.table_schema+%2B+'.'+%2B+tbl.table_name,+
++++TableDescription+=+prop.value,
++++ColumnName+=+col.column_name,+
++++ColumnDataType+=+col.data_type
FROM+information_schema.tables+tbl
INNER+JOIN+information_schema.columns+col+
++++ON+col.table_name+=+tbl.table_name
++++AND+col.table_schema+=+tbl.table_schema
LEFT+JOIN+sys.extended_properties+prop+
++++ON+prop.major_id+=+object_id(tbl.table_schema+%2B+'.'+%2B+tbl.table_name)+
++++AND+prop.minor_id+=+0
++++AND+prop.name+=+'MS_Description'+
WHERE+tbl.table_type+=+'base+table'|3467845|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Generic information about tables and columns can be found in these tables:

<pre><code>select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS
</code></pre>

The table description is an extended property, you can query them from sys.extended_properties:

<pre><code>select 
 TableName = tbl.table_schema + '.' + tbl.table_name, 
 TableDescription = prop.value,
 ColumnName = col.column_name, 
 ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
 ON col.table_name = tbl.table_name
 AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop 
 ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
 AND prop.minor_id = 0
 AND prop.name = 'MS_Description' 
WHERE tbl.table_type = 'base table'
</code></pre>

blocks|key|3254761|text|不幸的是，要获取描述数据，您必须使用sysobjects/syscolumn来获取ids：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3254762|SELECT++++++u.name+%2B+'.'+%2B+t.name+AS+[table],
++++++++++++td.value+AS+[table_desc],
++++++++++++c.name+AS+[column],
++++++++++++cd.value+AS+[column_desc]
FROM++++++++sysobjects+t
INNER+JOIN++sysusers+u
++++ON++++++u.uid+=+t.uid
LEFT+OUTER+JOIN+sys.extended_properties+td
++++ON++++++td.major_id+=+t.id
++++AND+++++td.minor_id+=+0
++++AND+++++td.name+=+'MS_Description'
INNER+JOIN++syscolumns+c
++++ON++++++c.id+=+t.id
LEFT+OUTER+JOIN+sys.extended_properties+cd
++++ON++++++cd.major_id+=+c.id
++++AND+++++cd.minor_id+=+c.colid
++++AND+++++cd.name+=+'MS_Description'
WHERE+t.type+=+'u'
ORDER+BY++++t.name,+c.colorder|code-block|syntax|javascript|3254763|你可以用info-schema来做这件事，但是你必须连接etc来调用OBJECT_ID()+-那么重点是什么呢？|3254764|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

To get the description data, you unfortunately have to use sysobjects/syscolumns to get the ids:

<pre><code>SELECT u.name + '.' + t.name AS [table],
 td.value AS [table_desc],
 c.name AS [column],
 cd.value AS [column_desc]
FROM sysobjects t
INNER JOIN sysusers u
 ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
 ON td.major_id = t.id
 AND td.minor_id = 0
 AND td.name = 'MS_Description'
INNER JOIN syscolumns c
 ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
 ON cd.major_id = c.id
 AND cd.minor_id = c.colid
 AND cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY t.name, c.colorder
</code></pre>

You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID() - so what would be the point?

blocks|key|3254792|text|你可以试试sp_help+<Name+of+object>|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3254793|entityMap^0|5|O|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You could try <code>sp_help &lt;Name of object&gt;</code>

blocks|key|3254833|text|使用对象目录视图的：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3254834|SELECT++T.NAME+AS+[TABLE+NAME],+C.NAME+AS+[COLUMN+NAME],+P.NAME+AS+[DATA+TYPE],+P.MAX_LENGTH+AS[SIZE],+++CAST(P.PRECISION+AS+VARCHAR)+%2B‘/’%2B+CAST(P.SCALE+AS+VARCHAR)+AS+[PRECISION/SCALE]
FROM+ADVENTUREWORKS.SYS.OBJECTS+AS+T
JOIN+ADVENTUREWORKS.SYS.COLUMNS+AS+C
ON+T.OBJECT_ID=C.OBJECT_ID
JOIN+ADVENTUREWORKS.SYS.TYPES+AS+P
ON+C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE+T.TYPE_DESC=‘USER_TABLE’;|code-block|syntax|javascript|3254835|使用信息架构视图的|3254836|SELECT+TABLE_SCHEMA,+TABLE_NAME,+COLUMN_NAME,+ORDINAL_POSITION,
+++++++COLUMN_DEFAULT,+DATA_TYPE,+CHARACTER_MAXIMUM_LENGTH,
+++++++NUMERIC_PRECISION,+NUMERIC_PRECISION_RADIX,+NUMERIC_SCALE,
+++++++DATETIME_PRECISION
FROM+ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS|3254837|entityMap^0|9|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Using Object Catalog Views:

<pre><code>SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;
</code></pre>

Using Information Schema Views

<pre><code>SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
 COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
 NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
 DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
</code></pre>

blocks|key|413787|text|如果您只是想以一种方便的方式查看信息，Red+Gate的SQL提示符可能会有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|413788|如果将鼠标悬停在查询窗口中的对象文本上，SQL提示符将在工具提示中显示MS_Description扩展属性文本。单击工具提示将打开一个对话框，其中显示列信息以及对象的DDL。|413789|http://www.red-gate.com/products/sql-development/sql-prompt/|offset|length|413790|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1O|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

If you simply want to view the information in a convenient way, Red Gate's SQL Prompt might help. 

If you hover over the object text in a query window SQL Prompt will display the MS_Description extended property text in a tooltip. Clicking on the tooltip will open a dialog displaying the column information and also the object's DDL.

<a href="http://www.red-gate.com/products/sql-development/sql-prompt/" rel="nofollow">http://www.red-gate.com/products/sql-development/sql-prompt/</a>

blocks|key|413795|text|如果可以使用表代码，我建议使用SMO：http://msdn.microsoft.com/en-us/library/ms162169.aspx，在您的特定情况下应该使用.NET类http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx这将是一个比使用特定版本的系统视图和表更可移植的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|413796|如果这是您要经常使用的东西--您可能想要编写一个简单的控制台应用程序，可能需要使用运行时T4代码生成器http://msdn.microsoft.com/en-us/library/ee844259.aspx|413797|如果这只是一个一次性的任务-你可以使用我的LiveDoco(+http://www.livedoco.com+)的导出到XML特性和一个可选的XSLT转换，或者我相信市面上有免费的工具可以做到这一点。这个看起来还不错：http://sqldbdoc.codeplex.com/+-通过XSLT支持XML，但是我不确定您是否可以对一些表运行它(使用LiveDoco可以)。|413798|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms162169.aspx|1|http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx|2|http://msdn.microsoft.com/en-us/library/ee844259.aspx|3|http://www.livedoco.com/|4|http://sqldbdoc.codeplex.com/^0|J|1H|0|2I|2D|1|0|1F|1H|2|0|V|N|3|31|T|4|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]|$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|F|3|G|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]|$A|1C|B|1D|1|1E]]|C|$]]|$1|H|3|-4|5|6|7|1F|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]|R|$5|K|L|M|C|$N|S]]|T|$5|K|L|M|C|$N|U]]|V|$5|K|L|M|C|$N|W]]]]

If it is OK to use .NET code I'd suggest using SMO: <a href="http://msdn.microsoft.com/en-us/library/ms162169.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms162169.aspx</a>, In your particular case it would be the Table class <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx</a>
This would be a more portable solution than using version specific system views and tables.

If this is something you are going to use on a regular basis - you might want to write a simple console application, perhaps with a runtime T4 code generator <a href="http://msdn.microsoft.com/en-us/library/ee844259.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ee844259.aspx</a>

If it's just a one-off task - you could use my LiveDoco's( <a href="http://www.livedoco.com" rel="nofollow">http://www.livedoco.com</a> ) export to XML feature with an optional XSLT transform or I'm sure there are free tools out there that can do this. 
This one looks okay: <a href="http://sqldbdoc.codeplex.com/" rel="nofollow">http://sqldbdoc.codeplex.com/</a> - supports XML via XSLT, but I'm not sure if you can run it for a selection of tables though (With LiveDoco you can).

blocks|key|416497|text|如果您正在使用java代码提取查询，那么可以使用一个很棒的类ResultSetMetaData，它可以检索列名和列的属性(类型和长度)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|416498|示例|offset|length|style|BOLD|416499|ResultSet+rs+=+null;

++++++++rs+=+sql.executeQuery();

++++++++if+(rs+!=+null)+{
++++++++++++if+(rs.next())+{
++++++++++++++++ResultSetMetaData+rsmd+=+rs.getMetaData();
++++++++++++++++for+(int+i+=+1;+i+<=+rsmd.getColumnCount();+i%2B%2B)+{
++++++++++++++++++++System.out.println("column+name:+"
++++++++++++++++++++++++++++%2B+rsmd.getColumnName(i));
++++++++++++++++++++System.out.println("column+size:+"
++++++++++++++++++++++++++++%2B+rsmd.getColumnDisplaySize(i));
++++++++++++++++}
++++++++++++}|code-block|syntax|javascript|416500|entityMap^0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you are pulling your queries using java code, there is a great class that can be used, ResultSetMetaData, that can retrieve column names and the properties of the columns (type and length).

Example

<pre><code>ResultSet rs = null;

 rs = sql.executeQuery();

 if (rs != null) {
 if (rs.next()) {
 ResultSetMetaData rsmd = rs.getMetaData();
 for (int i = 1; i &lt;= rsmd.getColumnCount(); i++) {
 System.out.println("column name: "
 + rsmd.getColumnName(i));
 System.out.println("column size: "
 + rsmd.getColumnDisplaySize(i));
 }
 }
</code></pre>

blocks|key|410803|text|我使用这段SQL代码来获取一个列的所有信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410804|SELECT
COL.COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE,
CONSTRAINT_TYPE,
COLUMNPROPERTY(object_id(COL.TABLE_NAME),+COL.COLUMN_NAME,+'IsIdentity')+IS_IDENTITY,
COLUMNPROPERTY(object_id(COL.TABLE_NAME),+COL.COLUMN_NAME,+'IsComputed')+IS_COMPUTED

FROM+INFORMATION_SCHEMA.COLUMNS+COL+
LEFT+OUTER+JOIN+
(
++++SELECT+COLUMN_NAME,+CONSTRAINT_TYPE+
++++FROM+INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+A
++++INNER+JOIN+INFORMATION_SCHEMA.TABLE_CONSTRAINTS+B+
++++ON+A.CONSTRAINT_NAME+=+B.CONSTRAINT_NAME
++++WHERE+A.TABLE_NAME+=+'User'
)+CONS
ON+COL.COLUMN_NAME+=+CONS.COLUMN_NAME
WHERE+COL.TABLE_NAME+=+'User'|code-block|syntax|javascript|410805|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I use this SQL code to get all the information about a column.

<pre><code>SELECT
COL.COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE,
CONSTRAINT_TYPE,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsIdentity') IS_IDENTITY,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsComputed') IS_COMPUTED

FROM INFORMATION_SCHEMA.COLUMNS COL 
LEFT OUTER JOIN 
(
 SELECT COLUMN_NAME, CONSTRAINT_TYPE 
 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B 
 ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 WHERE A.TABLE_NAME = 'User'
) CONS
ON COL.COLUMN_NAME = CONS.COLUMN_NAME
WHERE COL.TABLE_NAME = 'User'
</code></pre>

blocks|key|414151|text|++SELECT
++++sc.name+AS+ColumnName
+++,ep.*
++FROM
++++sys.columns+AS+sc
++++INNER+JOIN+sys.extended_properties+AS+ep
++++++ON+ep.major_id+=+sc.[object_id]
+++++++++AND+ep.minor_id+=+sc.column_id
++WHERE

--here+put+your+desired+table
++++sc.[object_id]+=+OBJECT_ID('[Northwind].[dbo].[Products]')

--+this+is+optional,+remove+this+and+you+get+all+extended+props
++++AND+ep.name+=+'MS_Description'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|414152|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> SELECT
 sc.name AS ColumnName
 ,ep.*
 FROM
 sys.columns AS sc
 INNER JOIN sys.extended_properties AS ep
 ON ep.major_id = sc.[object_id]
 AND ep.minor_id = sc.column_id
 WHERE

--here put your desired table
 sc.[object_id] = OBJECT_ID('[Northwind].[dbo].[Products]')

-- this is optional, remove this and you get all extended props
 AND ep.name = 'MS_Description'
</code></pre>

blocks|key|3255009|text|根据您想要多少元数据，这对我很有效:哪里可以是像Northwind.dbo.Products这样的所有东西，或者仅仅是产品|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3255010|SELECT+c.name+Field,+
++++t.name+Type,
++++c.Precision,+
++++c.Scale,
++++c.is_nullable,
++++c.collation_name+
FROM+sys.columns+c+
INNER+JOIN+sys.types+t+ON+t.system_type_id=c.system_type_id
WHERE+object_id=object_id('<table+to+inspect>')
ORDER+BY+column_id|code-block|syntax|javascript|3255011|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Depending on how much metadata you want, this is working for me:
where could be the whole thing like Northwind.dbo.Products or just Products

<pre><code>SELECT c.name Field, 
 t.name Type,
 c.Precision, 
 c.Scale,
 c.is_nullable,
 c.collation_name 
FROM sys.columns c 
INNER JOIN sys.types t ON t.system_type_id=c.system_type_id
WHERE object_id=object_id('&lt;table to inspect&gt;')
ORDER BY column_id
</code></pre>

blocks|key|410830|text|select+Col.name+Columnname,prop.Value+Description,+tbl.name+Tablename,+sch.name+schemaname
++++from+sys.columns+col++left+outer+join++sys.extended_properties+prop
++++++++++++++++++++on+prop.major_id+=++col.object_id+and+prop.minor_id+=+col.column_id
++++++++++++++++++++inner+join+sys.tables+tbl+on+col.object_id+=++tbl.object_id
++++++++++++++++++++Left+outer+join+sys.schemas+sch+on+sch.schema_id+=+tbl.schema_id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|410831|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select Col.name Columnname,prop.Value Description, tbl.name Tablename, sch.name schemaname
 from sys.columns col left outer join sys.extended_properties prop
 on prop.major_id = col.object_id and prop.minor_id = col.column_id
 inner join sys.tables tbl on col.object_id = tbl.object_id
 Left outer join sys.schemas sch on sch.schema_id = tbl.schema_id
</code></pre>

blocks|key|3255039|text|我最喜欢@Andomar的答案，但我也需要专栏的描述。以下是他的查询，修改后也包括了这些内容。(取消对WHERE子句最后一部分的注释，以便只返回任何一个描述都不为空的行)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3255040|SELECT
++++TableName+=+tbl.table_schema+%2B+'.'+%2B+tbl.table_name,+
++++TableDescription+=+tableProp.value,
++++ColumnName+=+col.column_name,+
++++ColumnDataType+=+col.data_type,
++++ColumnDescription+=+colDesc.ColumnDescription
FROM+information_schema.tables+tbl
INNER+JOIN+information_schema.columns+col+
++++ON+col.table_name+=+tbl.table_name
LEFT+JOIN+sys.extended_properties+tableProp+
++++ON+tableProp.major_id+=+object_id(tbl.table_schema+%2B+'.'+%2B+tbl.table_name)+
++++++++AND+tableProp.minor_id+=+0
++++++++AND+tableProp.name+=+'MS_Description'+
LEFT+JOIN+(
++++SELECT+sc.object_id,+sc.column_id,+sc.name,+colProp.[value]+AS+ColumnDescription
++++FROM+sys.columns+sc
++++INNER+JOIN+sys.extended_properties+colProp
++++++++ON+colProp.major_id+=+sc.object_id
++++++++++++AND+colProp.minor_id+=+sc.column_id
++++++++++++AND+colProp.name+=+'MS_Description'+
)+colDesc
++++ON+colDesc.object_id+=+object_id(tbl.table_schema+%2B+'.'+%2B+tbl.table_name)
++++++++AND+colDesc.name+=+col.COLUMN_NAME
WHERE+tbl.table_type+=+'base+table'
--AND+tableProp.[value]+IS+NOT+NULL+OR+colDesc.ColumnDescription+IS+NOT+null|code-block|syntax|javascript|3255041|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I liked @Andomar's answer best, but I needed the column descriptions also. Here is his query modified to include those also. (Uncomment the last part of the WHERE clause to return only rows where either description is non-null).

<pre><code>SELECT
 TableName = tbl.table_schema + '.' + tbl.table_name, 
 TableDescription = tableProp.value,
 ColumnName = col.column_name, 
 ColumnDataType = col.data_type,
 ColumnDescription = colDesc.ColumnDescription
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
 ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties tableProp 
 ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
 AND tableProp.minor_id = 0
 AND tableProp.name = 'MS_Description' 
LEFT JOIN (
 SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription
 FROM sys.columns sc
 INNER JOIN sys.extended_properties colProp
 ON colProp.major_id = sc.object_id
 AND colProp.minor_id = sc.column_id
 AND colProp.name = 'MS_Description' 
) colDesc
 ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
 AND colDesc.name = col.COLUMN_NAME
WHERE tbl.table_type = 'base table'
--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null
</code></pre>

blocks|key|414251|text|我刚刚使用一些有用的查询完成了一个.net库，这些查询返回代码生成/+t4模板的强类型C#对象。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|414252|nuget+SqlMeta|offset|length|414253|Project+Site|414254|github+source|414255|///+<summary>
++++///+++++Get+All+Table+Names
++++///+</summary>
++++///+<returns></returns>
++++public+List<string>+GetTableNames()
++++{
++++++++var+sql+=+@"SELECT+name
++++++++++++++++++++FROM+dbo.sysobjects
++++++++++++++++++++WHERE+xtype+=+'U'+
++++++++++++++++++++AND+name+<>+'sysdiagrams'
++++++++++++++++++++order+by+name+asc";

++++++++return+databaseWrapper.Call(connection+=>+connection.Query<string>(
++++++++++++sql:+sql))
++++++++++++.ToList();
++++}

++++///+<summary>
++++///+++++Get+table+info+by+schema+and+table+or+null+for+all
++++///+</summary>
++++///+<param+name="schema"></param>
++++///+<param+name="table"></param>
++++///+<returns></returns>
++++public+List<SqlTableInfo>+GetTableInfo(string+schema+=+null,+string+table+=+null)
++++{
++++++++var+result+=+new+List<SqlTableInfo>();

++++++++var+sql+=+@"SELECT
++++++++++++++++++++c.TABLE_CATALOG+AS+[TableCatalog]
++++++++++++++++,+++c.TABLE_SCHEMA+AS+[Schema]
++++++++++++++++,+++c.TABLE_NAME+AS+[TableName]
++++++++++++++++,+++c.COLUMN_NAME+AS+[ColumnName]
++++++++++++++++,+++c.ORDINAL_POSITION+AS+[OrdinalPosition]
++++++++++++++++,+++c.COLUMN_DEFAULT+AS+[ColumnDefault]
++++++++++++++++,+++c.IS_NULLABLE+AS+[Nullable]
++++++++++++++++,+++c.DATA_TYPE+AS+[DataType]
++++++++++++++++,+++c.CHARACTER_MAXIMUM_LENGTH+AS+[CharacterMaxLength]
++++++++++++++++,+++c.CHARACTER_OCTET_LENGTH+AS+[CharacterOctetLenth]
++++++++++++++++,+++c.NUMERIC_PRECISION+AS+[NumericPrecision]
++++++++++++++++,+++c.NUMERIC_PRECISION_RADIX+AS+[NumericPrecisionRadix]
++++++++++++++++,+++c.NUMERIC_SCALE+AS+[NumericScale]
++++++++++++++++,+++c.DATETIME_PRECISION+AS+[DatTimePrecision]
++++++++++++++++,+++c.CHARACTER_SET_CATALOG+AS+[CharacterSetCatalog]
++++++++++++++++,+++c.CHARACTER_SET_SCHEMA+AS+[CharacterSetSchema]
++++++++++++++++,+++c.CHARACTER_SET_NAME+AS+[CharacterSetName]
++++++++++++++++,+++c.COLLATION_CATALOG+AS+[CollationCatalog]
++++++++++++++++,+++c.COLLATION_SCHEMA+AS+[CollationSchema]
++++++++++++++++,+++c.COLLATION_NAME+AS+[CollationName]
++++++++++++++++,+++c.DOMAIN_CATALOG+AS+[DomainCatalog]
++++++++++++++++,+++c.DOMAIN_SCHEMA+AS+[DomainSchema]
++++++++++++++++,+++c.DOMAIN_NAME+AS+[DomainName]
++++++++++++++++,+++IsPrimaryKey+=+CONVERT(BIT,+(SELECT
++++++++++++++++++++++++++++COUNT(*)
++++++++++++++++++++++++FROM++++INFORMATION_SCHEMA.TABLE_CONSTRAINTS+tc
++++++++++++++++++++++++++++,+++INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+cu
++++++++++++++++++++++++WHERE+CONSTRAINT_TYPE+=+'PRIMARY+KEY'
++++++++++++++++++++++++AND+tc.CONSTRAINT_NAME+=+cu.CONSTRAINT_NAME
++++++++++++++++++++++++AND+tc.TABLE_NAME+=+c.TABLE_NAME
++++++++++++++++++++++++AND+cu.TABLE_SCHEMA+=+c.TABLE_SCHEMA
++++++++++++++++++++++++AND+cu.COLUMN_NAME+=+c.COLUMN_NAME)
++++++++++++++++++++)
++++++++++++++++,+++IsIdentity+=+CONVERT(BIT,+(SELECT
++++++++++++++++++++++++++++COUNT(*)
++++++++++++++++++++++++FROM+sys.objects+obj
++++++++++++++++++++++++INNER+JOIN+sys.COLUMNS+col
++++++++++++++++++++++++++++ON+obj.object_id+=+col.object_id
++++++++++++++++++++++++WHERE+obj.type+=+'U'
++++++++++++++++++++++++AND+obj.Name+=+c.TABLE_NAME
++++++++++++++++++++++++AND+col.Name+=+c.COLUMN_NAME
++++++++++++++++++++++++AND+col.is_identity+=+1)
++++++++++++++++++++)
++++++++++++++++FROM+INFORMATION_SCHEMA.COLUMNS+c
++++++++++++++++WHERE+(@Schema+IS+NULL
++++++++++++++++++++++++OR+c.TABLE_SCHEMA+=+@Schema)
++++++++++++++++++++AND+(@TableName+IS+NULL
++++++++++++++++++++++++OR+c.TABLE_NAME+=+@TableName)
++++++++++++++++++++";

++++++++var+columns+=+databaseWrapper.Call(connection+=>+connection.Query<SqlColumnInfo>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+Schema+=+schema,+TableName+=+table+},
++++++++++++commandType:+CommandType.Text)
++++++++++++.ToList());

++++++++var+refs+=+this.GetReferentialConstraints(table:+table,+schema:+schema);

++++++++foreach+(var+tableName+in+columns.Select(info+=>+info.TableName).Distinct())
++++++++{
++++++++++++var+tableColumns+=+columns.Where(info+=>+info.TableName+==+tableName).ToList();
++++++++++++var+children+=+refs.Where(c+=>+c.UniqueTableName+==+tableName).ToList();
++++++++++++var+parents+=+refs.Where(c+=>+c.TableName+==+tableName).ToList();
++++++++++++result.Add(new+SqlTableInfo
++++++++++++{
++++++++++++++++TableName+=+tableName,
++++++++++++++++Columns+=+tableColumns,
++++++++++++++++ChildConstraints+=+children,
++++++++++++++++ParentConstraints+=+parents
++++++++++++});

++++++++}

++++++++return+result;
++++}

++++public+List<SqlReferentialConstraint>+GetReferentialConstraints(string+table+=+null,+string+schema+=+null)
++++{
++++++++//https://technet.microsoft.com/en-us/library/aa175805%2528v=sql.80%2529.aspx
++++++++//https://technet.microsoft.com/en-us/library/Aa175805.312ron1%2528l=en-us,v=sql.80%2529.jpg
++++++++//https://msdn.microsoft.com/en-us/library/ms186778.aspx

++++++++var+sql+=+@"
++++++++++++++++++++SELECT
++++++++++++++++++++++++KCU1.CONSTRAINT_NAME+AS+[ConstraintName]
++++++++++++++++++++,+++KCU1.TABLE_NAME+AS+[TableName]
++++++++++++++++++++,+++KCU1.COLUMN_NAME+AS+[ColumnName]
++++++++++++++++++++,+++KCU2.CONSTRAINT_NAME+AS+[UniqueConstraintName]
++++++++++++++++++++,+++KCU2.TABLE_NAME+AS+[UniqueTableName]
++++++++++++++++++++,+++KCU2.COLUMN_NAME+AS+[UniqueColumnName]
++++++++++++++++++++,+++RC.MATCH_OPTION+AS+[MatchOption]
++++++++++++++++++++,+++RC.UPDATE_RULE+AS+[UpdateRule]
++++++++++++++++++++,+++RC.DELETE_RULE+AS+[DeleteRule]
++++++++++++++++++++FROM+INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS+RC
++++++++++++++++++++LEFT+JOIN+INFORMATION_SCHEMA.KEY_COLUMN_USAGE+KCU1+ON+KCU1.CONSTRAINT_CATALOG+=+RC.CONSTRAINT_CATALOG
++++++++++++++++++++++++AND+KCU1.CONSTRAINT_SCHEMA+=+RC.CONSTRAINT_SCHEMA
++++++++++++++++++++++++AND+KCU1.CONSTRAINT_NAME+=+RC.CONSTRAINT_NAME
++++++++++++++++++++LEFT+JOIN+INFORMATION_SCHEMA.KEY_COLUMN_USAGE+KCU2+ON+KCU2.CONSTRAINT_CATALOG+=+RC.UNIQUE_CONSTRAINT_CATALOG
++++++++++++++++++++++++AND+KCU2.CONSTRAINT_SCHEMA+=+RC.UNIQUE_CONSTRAINT_SCHEMA
++++++++++++++++++++++++AND+KCU2.CONSTRAINT_NAME+=+RC.UNIQUE_CONSTRAINT_NAME
++++++++++++++++++++WHERE+KCU1.ORDINAL_POSITION+=+KCU2.ORDINAL_POSITION
++++++++++++++++++++++++++++AND+(@Table+IS+NULL
++++++++++++++++++++++++++++++++OR+KCU1.TABLE_NAME+=+@Table
++++++++++++++++++++++++++++++++OR+KCU2.TABLE_NAME+=+@Table)
++++++++++++++++++++++++++++AND+(@Schema+IS+NULL
++++++++++++++++++++++++++++++++OR+KCU1.TABLE_SCHEMA+=+@Schema
++++++++++++++++++++++++++++++++OR+KCU2.TABLE_SCHEMA+=+@Schema)
++++++++++++++++++++";

++++++++return+databaseWrapper.Call(connection+=>+connection.Query<SqlReferentialConstraint>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+Table+=+table,+Schema+=+schema+},
++++++++++++commandType:+CommandType.Text))
++++++++++++.ToList();
++++}

++++///+<summary>
++++///+++++Get+Primary+Key+Column+by+schema+and+table+name
++++///+</summary>
++++///+<param+name="schema"></param>
++++///+<param+name="tableName"></param>
++++///+<returns></returns>
++++public+string+GetPrimaryKeyColumnName(string+schema,+string+tableName)
++++{
++++++++var+sql+=+@"SELECT
++++++++++++++++++++B.COLUMN_NAME
++++++++++++++++FROM++++INFORMATION_SCHEMA.TABLE_CONSTRAINTS+A
++++++++++++++++++++,+++INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+B
++++++++++++++++WHERE+CONSTRAINT_TYPE+=+'PRIMARY+KEY'
++++++++++++++++++++AND+A.CONSTRAINT_NAME+=+B.CONSTRAINT_NAME
++++++++++++++++++++AND+A.TABLE_NAME+=+@TableName
++++++++++++++++++++AND+A.TABLE_SCHEMA+=+@Schema";

++++++++return+databaseWrapper.Call(connection+=>+connection.Query<string>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+TableName+=+tableName,+Schema+=+schema+},
++++++++++++commandType:+CommandType.Text))
++++++++++++.SingleOrDefault();
++++}

++++///+<summary>
++++///+++++Get+Identity+Column+by+table+name
++++///+</summary>
++++///+<param+name="tableName"></param>
++++///+<returns></returns>
++++public+string+GetIdentityColumnName(string+tableName)
++++{
++++++++var+sql+=+@"SELECT
++++++++++++++++++++c.Name
++++++++++++++++FROM+sys.objects+o
++++++++++++++++INNER+JOIN+sys.columns+c+ON+o.object_id+=+c.object_id
++++++++++++++++WHERE+o.type+=+'U'
++++++++++++++++++++AND+c.is_identity+=+1
++++++++++++++++++++AND+o.Name+=+@TableName";

++++++++return+databaseWrapper.Call(connection+=>+connection.Query<string>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+TableName+=+tableName+},
++++++++++++commandType:+CommandType.Text))
++++++++++++.SingleOrDefault();
++++}

++++///+<summary>
++++///+++++Get+All+Stored+Procedures+by+schema
++++///+</summary>
++++///+<param+name="schema"></param>
++++///+<param+name="procName"></param>
++++///+<returns></returns>
++++public+List<SqlStoredProcedureInfo>+GetStoredProcedureInfo(string+schema+=+null,+string+procName+=+null)
++++{
++++++++var+result+=+new+List<SqlStoredProcedureInfo>();

++++++++var+sql+=+@"SELECT
++++++++++++++++++++++++SPECIFIC_NAME+AS+[Name]
++++++++++++++++++++,+++SPECIFIC_SCHEMA+AS+[Schema]
++++++++++++++++++++,+++Created+AS+[Created]
++++++++++++++++++++,+++LAST_ALTERED+AS+[LastAltered]
++++++++++++++++++++FROM+INFORMATION_SCHEMA.ROUTINES
++++++++++++++++++++WHERE+ROUTINE_TYPE+=+'PROCEDURE'
++++++++++++++++++++++++AND+(SPECIFIC_SCHEMA+=+@Schema
++++++++++++++++++++++++++++OR+@Schema+IS+NULL)
++++++++++++++++++++++++AND+(SPECIFIC_NAME+=+@ProcName
++++++++++++++++++++++++++++OR+@ProcName+IS+NULL)
++++++++++++++++++++++++AND+((SPECIFIC_NAME+NOT+LIKE+'sp_%25'
++++++++++++++++++++++++++++++++AND+SPECIFIC_NAME+NOT+LIKE+'procUtils_GenerateClass'
++++++++++++++++++++++++++++++++AND+(SPECIFIC_SCHEMA+=+@Schema
++++++++++++++++++++++++++++++++++++OR+@Schema+IS+NULL))
++++++++++++++++++++++++++++OR+SPECIFIC_SCHEMA+<>+@Schema)";

++++++++var+sprocs+=+databaseWrapper.Call(connection+=>+connection.Query<SqlStoredProcedureInfo>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+Schema+=+schema,+ProcName+=+procName+},
++++++++++++commandType:+CommandType.Text).ToList());

++++++++foreach+(var+s+in+sprocs)
++++++++{
++++++++++++s.Parameters+=+GetStoredProcedureInputParameters(sprocName:+s.Name,+schema:+schema);
++++++++++++s.ResultColumns+=+GetColumnInfoFromStoredProcResult(storedProcName:+s.Name,+schema:+schema);
++++++++++++result.Add(s);
++++++++}

++++++++return+result;
++++}

++++///+<summary>
++++///+++++Get+Column+info+from+Stored+procedure+result+set
++++///+</summary>
++++///+<param+name="schema"></param>
++++///+<param+name="storedProcName"></param>
++++///+<returns></returns>
++++public+List<DataColumn>+GetColumnInfoFromStoredProcResult(string+schema,+string+storedProcName)
++++{
++++++++//this+one+actually+needs+to+use+the+dataset+because+it+has+the+only+accurate+information+about+columns+and+if+they+can+be+null+or+not.
++++++++var+sb+=+new+StringBuilder();
++++++++if+(!String.IsNullOrEmpty(schema))
++++++++{
++++++++++++sb.Append(String.Format("exec+[{0}].[{1}]+",+schema,+storedProcName));
++++++++}
++++++++else
++++++++{
++++++++++++sb.Append(String.Format("exec+[{0}]+",+storedProcName));
++++++++}

++++++++var+prms+=+GetStoredProcedureInputParameters(schema,+storedProcName);

++++++++var+count+=+1;
++++++++foreach+(var+param+in+prms)
++++++++{
++++++++++++sb.Append(String.Format("{0}=null",+param.Name));
++++++++++++if+(count+<+prms.Count)
++++++++++++{
++++++++++++++++sb.Append(",+");
++++++++++++}
++++++++++++count%2B%2B;
++++++++}

++++++++var+ds+=+new+DataSet();
++++++++using+(var+sqlConnection+=+(SqlConnection)databaseWrapper.GetOpenDbConnection())
++++++++{
++++++++++++using+(var+sqlAdapter+=+new+SqlDataAdapter(sb.ToString(),+sqlConnection))
++++++++++++{
++++++++++++++++if+(sqlConnection.State+!=+ConnectionState.Open)+sqlConnection.Open();

++++++++++++++++sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);

++++++++++++++++sqlConnection.Close();

++++++++++++++++sqlAdapter.FillSchema(ds,+SchemaType.Source,+"MyTable");
++++++++++++}
++++++++}

++++++++var+list+=+new+List<DataColumn>();
++++++++if+(ds.Tables.Count+>+0)
++++++++{
++++++++++++list+=+ds.Tables["MyTable"].Columns.Cast<DataColumn>().ToList();
++++++++}

++++++++return+list;
++++}

++++///+<summary>
++++///+++++Get+the+input+parameters+for+a+stored+procedure
++++///+</summary>
++++///+<param+name="schema"></param>
++++///+<param+name="sprocName"></param>
++++///+<returns></returns>
++++public+List<SqlParameterInfo>+GetStoredProcedureInputParameters(string+schema+=+null,+string+sprocName+=+null)
++++{
++++++++var+sql+=+@"SELECT
++++++++++++++++++++SCHEMA_NAME(schema_id)+AS+[Schema]
++++++++++++++++,+++P.Name+AS+Name
++++++++++++++++,+++@ProcName+AS+ProcedureName
++++++++++++++++,+++TYPE_NAME(P.user_type_id)+AS+[ParameterDataType]
++++++++++++++++,+++P.max_length+AS+[MaxLength]
++++++++++++++++,+++P.Precision+AS+[Precision]
++++++++++++++++,+++P.Scale+AS+Scale
++++++++++++++++,+++P.has_default_value+AS+HasDefaultValue
++++++++++++++++,+++P.default_value+AS+DefaultValue
++++++++++++++++,+++P.object_id+AS+ObjectId
++++++++++++++++,+++P.parameter_id+AS+ParameterId
++++++++++++++++,+++P.system_type_id+AS+SystemTypeId
++++++++++++++++,+++P.user_type_id+AS+UserTypeId
++++++++++++++++,+++P.is_output+AS+IsOutput
++++++++++++++++,+++P.is_cursor_ref+AS+IsCursor
++++++++++++++++,+++P.is_xml_document+AS+IsXmlDocument
++++++++++++++++,+++P.xml_collection_id+AS+XmlCollectionId
++++++++++++++++,+++P.is_readonly+AS+IsReadOnly
++++++++++++++++FROM+sys.objects+AS+SO
++++++++++++++++INNER+JOIN+sys.parameters+AS+P+ON+SO.object_id+=+P.object_id
++++++++++++++++WHERE+SO.object_id+IN+(SELECT
++++++++++++++++++++++++++++object_id
++++++++++++++++++++++++FROM+sys.objects
++++++++++++++++++++++++WHERE+type+IN+('P',+'FN'))
++++++++++++++++++++AND+(SO.Name+=+@ProcName
++++++++++++++++++++++++OR+@ProcName+IS+NULL)
++++++++++++++++++++AND+(SCHEMA_NAME(schema_id)+=+@Schema
++++++++++++++++++++++++OR+@Schema+IS+NULL)
++++++++++++++++ORDER+BY+P.parameter_id+ASC";

++++++++var+result+=+databaseWrapper.Call(connection+=>+connection.Query<SqlParameterInfo>(
++++++++++++sql:+sql,
++++++++++++param:+new+{+Schema+=+schema,+ProcName+=+sprocName+},
++++++++++++commandType:+CommandType.Text))
++++++++++++.ToList();

++++++++return+result;
++++}|code-block|syntax|javascript|414256|Foreign+Key+Metadata|414257|entityMap|0|LINK|mutability|MUTABLE|url|http://www.nuget.org/packages/SqlMeta/|1|http://www.achadwick.com/DeveloperBlog/Result/SqlMeta%2520Code%2520Generation%2520Tool|2|https://github.com/TheDev6/SqlMeta|3|https://technet.microsoft.com/en-us/library/aa175805%2528v=sql.80%2529.aspx^0|0|0|D|0|0|0|C|1|0|0|D|2|0|0|0|K|3|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|F|3|G|5|6|7|19|8|@]|9|@$D|1A|E|1B|1|1C]]|A|$]]|$1|H|3|I|5|6|7|1D|8|@]|9|@$D|1E|E|1F|1|1G]]|A|$]]|$1|J|3|K|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1I|8|@]|9|@$D|1J|E|1K|1|1L]]|A|$]]|$1|Q|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]|10|$5|T|U|V|A|$W|11]]|12|$5|T|U|V|A|$W|13]]]]

I just finished a .net library with a few useful queries that return strongly typed C# objects for code gen/ t4 templates.

<a href="http://www.nuget.org/packages/SqlMeta/" rel="nofollow">nuget SqlMeta</a>

<a href="http://www.achadwick.com/DeveloperBlog/Result/SqlMeta%20Code%20Generation%20Tool" rel="nofollow">Project Site</a>

<a href="https://github.com/TheDev6/SqlMeta" rel="nofollow">github source</a>

<pre><code>/// &lt;summary&gt;
 /// Get All Table Names
 /// &lt;/summary&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public List&lt;string&gt; GetTableNames()
 {
 var sql = @"SELECT name
 FROM dbo.sysobjects
 WHERE xtype = 'U' 
 AND name &lt;&gt; 'sysdiagrams'
 order by name asc";

 return databaseWrapper.Call(connection =&gt; connection.Query&lt;string&gt;(
 sql: sql))
 .ToList();
 }

 /// &lt;summary&gt;
 /// Get table info by schema and table or null for all
 /// &lt;/summary&gt;
 /// &lt;param name="schema"&gt;&lt;/param&gt;
 /// &lt;param name="table"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public List&lt;SqlTableInfo&gt; GetTableInfo(string schema = null, string table = null)
 {
 var result = new List&lt;SqlTableInfo&gt;();

 var sql = @"SELECT
 c.TABLE_CATALOG AS [TableCatalog]
 , c.TABLE_SCHEMA AS [Schema]
 , c.TABLE_NAME AS [TableName]
 , c.COLUMN_NAME AS [ColumnName]
 , c.ORDINAL_POSITION AS [OrdinalPosition]
 , c.COLUMN_DEFAULT AS [ColumnDefault]
 , c.IS_NULLABLE AS [Nullable]
 , c.DATA_TYPE AS [DataType]
 , c.CHARACTER_MAXIMUM_LENGTH AS [CharacterMaxLength]
 , c.CHARACTER_OCTET_LENGTH AS [CharacterOctetLenth]
 , c.NUMERIC_PRECISION AS [NumericPrecision]
 , c.NUMERIC_PRECISION_RADIX AS [NumericPrecisionRadix]
 , c.NUMERIC_SCALE AS [NumericScale]
 , c.DATETIME_PRECISION AS [DatTimePrecision]
 , c.CHARACTER_SET_CATALOG AS [CharacterSetCatalog]
 , c.CHARACTER_SET_SCHEMA AS [CharacterSetSchema]
 , c.CHARACTER_SET_NAME AS [CharacterSetName]
 , c.COLLATION_CATALOG AS [CollationCatalog]
 , c.COLLATION_SCHEMA AS [CollationSchema]
 , c.COLLATION_NAME AS [CollationName]
 , c.DOMAIN_CATALOG AS [DomainCatalog]
 , c.DOMAIN_SCHEMA AS [DomainSchema]
 , c.DOMAIN_NAME AS [DomainName]
 , IsPrimaryKey = CONVERT(BIT, (SELECT
 COUNT(*)
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
 WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
 AND tc.TABLE_NAME = c.TABLE_NAME
 AND cu.TABLE_SCHEMA = c.TABLE_SCHEMA
 AND cu.COLUMN_NAME = c.COLUMN_NAME)
 )
 , IsIdentity = CONVERT(BIT, (SELECT
 COUNT(*)
 FROM sys.objects obj
 INNER JOIN sys.COLUMNS col
 ON obj.object_id = col.object_id
 WHERE obj.type = 'U'
 AND obj.Name = c.TABLE_NAME
 AND col.Name = c.COLUMN_NAME
 AND col.is_identity = 1)
 )
 FROM INFORMATION_SCHEMA.COLUMNS c
 WHERE (@Schema IS NULL
 OR c.TABLE_SCHEMA = @Schema)
 AND (@TableName IS NULL
 OR c.TABLE_NAME = @TableName)
 ";

 var columns = databaseWrapper.Call(connection =&gt; connection.Query&lt;SqlColumnInfo&gt;(
 sql: sql,
 param: new { Schema = schema, TableName = table },
 commandType: CommandType.Text)
 .ToList());

 var refs = this.GetReferentialConstraints(table: table, schema: schema);

 foreach (var tableName in columns.Select(info =&gt; info.TableName).Distinct())
 {
 var tableColumns = columns.Where(info =&gt; info.TableName == tableName).ToList();
 var children = refs.Where(c =&gt; c.UniqueTableName == tableName).ToList();
 var parents = refs.Where(c =&gt; c.TableName == tableName).ToList();
 result.Add(new SqlTableInfo
 {
 TableName = tableName,
 Columns = tableColumns,
 ChildConstraints = children,
 ParentConstraints = parents
 });

 }

 return result;
 }

 public List&lt;SqlReferentialConstraint&gt; GetReferentialConstraints(string table = null, string schema = null)
 {
 //https://technet.microsoft.com/en-us/library/aa175805%28v=sql.80%29.aspx
 //https://technet.microsoft.com/en-us/library/Aa175805.312ron1%28l=en-us,v=sql.80%29.jpg
 //https://msdn.microsoft.com/en-us/library/ms186778.aspx

 var sql = @"
 SELECT
 KCU1.CONSTRAINT_NAME AS [ConstraintName]
 , KCU1.TABLE_NAME AS [TableName]
 , KCU1.COLUMN_NAME AS [ColumnName]
 , KCU2.CONSTRAINT_NAME AS [UniqueConstraintName]
 , KCU2.TABLE_NAME AS [UniqueTableName]
 , KCU2.COLUMN_NAME AS [UniqueColumnName]
 , RC.MATCH_OPTION AS [MatchOption]
 , RC.UPDATE_RULE AS [UpdateRule]
 , RC.DELETE_RULE AS [DeleteRule]
 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
 AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
 AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
 AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
 AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
 WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
 AND (@Table IS NULL
 OR KCU1.TABLE_NAME = @Table
 OR KCU2.TABLE_NAME = @Table)
 AND (@Schema IS NULL
 OR KCU1.TABLE_SCHEMA = @Schema
 OR KCU2.TABLE_SCHEMA = @Schema)
 ";

 return databaseWrapper.Call(connection =&gt; connection.Query&lt;SqlReferentialConstraint&gt;(
 sql: sql,
 param: new { Table = table, Schema = schema },
 commandType: CommandType.Text))
 .ToList();
 }

 /// &lt;summary&gt;
 /// Get Primary Key Column by schema and table name
 /// &lt;/summary&gt;
 /// &lt;param name="schema"&gt;&lt;/param&gt;
 /// &lt;param name="tableName"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public string GetPrimaryKeyColumnName(string schema, string tableName)
 {
 var sql = @"SELECT
 B.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
 , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
 WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 AND A.TABLE_NAME = @TableName
 AND A.TABLE_SCHEMA = @Schema";

 return databaseWrapper.Call(connection =&gt; connection.Query&lt;string&gt;(
 sql: sql,
 param: new { TableName = tableName, Schema = schema },
 commandType: CommandType.Text))
 .SingleOrDefault();
 }

 /// &lt;summary&gt;
 /// Get Identity Column by table name
 /// &lt;/summary&gt;
 /// &lt;param name="tableName"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public string GetIdentityColumnName(string tableName)
 {
 var sql = @"SELECT
 c.Name
 FROM sys.objects o
 INNER JOIN sys.columns c ON o.object_id = c.object_id
 WHERE o.type = 'U'
 AND c.is_identity = 1
 AND o.Name = @TableName";

 return databaseWrapper.Call(connection =&gt; connection.Query&lt;string&gt;(
 sql: sql,
 param: new { TableName = tableName },
 commandType: CommandType.Text))
 .SingleOrDefault();
 }

 /// &lt;summary&gt;
 /// Get All Stored Procedures by schema
 /// &lt;/summary&gt;
 /// &lt;param name="schema"&gt;&lt;/param&gt;
 /// &lt;param name="procName"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public List&lt;SqlStoredProcedureInfo&gt; GetStoredProcedureInfo(string schema = null, string procName = null)
 {
 var result = new List&lt;SqlStoredProcedureInfo&gt;();

 var sql = @"SELECT
 SPECIFIC_NAME AS [Name]
 , SPECIFIC_SCHEMA AS [Schema]
 , Created AS [Created]
 , LAST_ALTERED AS [LastAltered]
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'
 AND (SPECIFIC_SCHEMA = @Schema
 OR @Schema IS NULL)
 AND (SPECIFIC_NAME = @ProcName
 OR @ProcName IS NULL)
 AND ((SPECIFIC_NAME NOT LIKE 'sp_%'
 AND SPECIFIC_NAME NOT LIKE 'procUtils_GenerateClass'
 AND (SPECIFIC_SCHEMA = @Schema
 OR @Schema IS NULL))
 OR SPECIFIC_SCHEMA &lt;&gt; @Schema)";

 var sprocs = databaseWrapper.Call(connection =&gt; connection.Query&lt;SqlStoredProcedureInfo&gt;(
 sql: sql,
 param: new { Schema = schema, ProcName = procName },
 commandType: CommandType.Text).ToList());

 foreach (var s in sprocs)
 {
 s.Parameters = GetStoredProcedureInputParameters(sprocName: s.Name, schema: schema);
 s.ResultColumns = GetColumnInfoFromStoredProcResult(storedProcName: s.Name, schema: schema);
 result.Add(s);
 }

 return result;
 }

 /// &lt;summary&gt;
 /// Get Column info from Stored procedure result set
 /// &lt;/summary&gt;
 /// &lt;param name="schema"&gt;&lt;/param&gt;
 /// &lt;param name="storedProcName"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public List&lt;DataColumn&gt; GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
 {
 //this one actually needs to use the dataset because it has the only accurate information about columns and if they can be null or not.
 var sb = new StringBuilder();
 if (!String.IsNullOrEmpty(schema))
 {
 sb.Append(String.Format("exec [{0}].[{1}] ", schema, storedProcName));
 }
 else
 {
 sb.Append(String.Format("exec [{0}] ", storedProcName));
 }

 var prms = GetStoredProcedureInputParameters(schema, storedProcName);

 var count = 1;
 foreach (var param in prms)
 {
 sb.Append(String.Format("{0}=null", param.Name));
 if (count &lt; prms.Count)
 {
 sb.Append(", ");
 }
 count++;
 }

 var ds = new DataSet();
 using (var sqlConnection = (SqlConnection)databaseWrapper.GetOpenDbConnection())
 {
 using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
 {
 if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();

 sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);

 sqlConnection.Close();

 sqlAdapter.FillSchema(ds, SchemaType.Source, "MyTable");
 }
 }

 var list = new List&lt;DataColumn&gt;();
 if (ds.Tables.Count &gt; 0)
 {
 list = ds.Tables["MyTable"].Columns.Cast&lt;DataColumn&gt;().ToList();
 }

 return list;
 }

 /// &lt;summary&gt;
 /// Get the input parameters for a stored procedure
 /// &lt;/summary&gt;
 /// &lt;param name="schema"&gt;&lt;/param&gt;
 /// &lt;param name="sprocName"&gt;&lt;/param&gt;
 /// &lt;returns&gt;&lt;/returns&gt;
 public List&lt;SqlParameterInfo&gt; GetStoredProcedureInputParameters(string schema = null, string sprocName = null)
 {
 var sql = @"SELECT
 SCHEMA_NAME(schema_id) AS [Schema]
 , P.Name AS Name
 , @ProcName AS ProcedureName
 , TYPE_NAME(P.user_type_id) AS [ParameterDataType]
 , P.max_length AS [MaxLength]
 , P.Precision AS [Precision]
 , P.Scale AS Scale
 , P.has_default_value AS HasDefaultValue
 , P.default_value AS DefaultValue
 , P.object_id AS ObjectId
 , P.parameter_id AS ParameterId
 , P.system_type_id AS SystemTypeId
 , P.user_type_id AS UserTypeId
 , P.is_output AS IsOutput
 , P.is_cursor_ref AS IsCursor
 , P.is_xml_document AS IsXmlDocument
 , P.xml_collection_id AS XmlCollectionId
 , P.is_readonly AS IsReadOnly
 FROM sys.objects AS SO
 INNER JOIN sys.parameters AS P ON SO.object_id = P.object_id
 WHERE SO.object_id IN (SELECT
 object_id
 FROM sys.objects
 WHERE type IN ('P', 'FN'))
 AND (SO.Name = @ProcName
 OR @ProcName IS NULL)
 AND (SCHEMA_NAME(schema_id) = @Schema
 OR @Schema IS NULL)
 ORDER BY P.parameter_id ASC";

 var result = databaseWrapper.Call(connection =&gt; connection.Query&lt;SqlParameterInfo&gt;(
 sql: sql,
 param: new { Schema = schema, ProcName = sprocName },
 commandType: CommandType.Text))
 .ToList();

 return result;
 }
</code></pre>

<a href="https://technet.microsoft.com/en-us/library/aa175805%28v=sql.80%29.aspx" rel="nofollow">Foreign Key Metadata</a>

blocks|key|410934|text|看看这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410935|SELECT+TABLE_SCHEMA+,
+++++++TABLE_NAME+,
+++++++COLUMN_NAME+,
+++++++ORDINAL_POSITION+,
+++++++COLUMN_DEFAULT+,
+++++++DATA_TYPE+,
+++++++CHARACTER_MAXIMUM_LENGTH+,
+++++++NUMERIC_PRECISION+,
+++++++NUMERIC_PRECISION_RADIX+,
+++++++NUMERIC_SCALE+,
+++++++DATETIME_PRECISION
FROM+++INFORMATION_SCHEMA.COLUMNS
where+TABLE_SCHEMA+in+('dbo','meta')
and+table_name+in+(select+name+from+sys.tables)
order+by+TABLE_SCHEMA+,+++++++TABLE_NAME+,ORDINAL_POSITION|code-block|syntax|javascript|410936|​|410937|📷|atomic|offset|length|410938|410939|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/556c5cdc3557cad57dc74621eb199a68.png|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|10|8|@]|9|@$L|11|M|12|1|13]]|A|$]]|$1|N|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V|W|-4]]]]

Check this out:

<pre><code>SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME ,
 ORDINAL_POSITION ,
 COLUMN_DEFAULT ,
 DATA_TYPE ,
 CHARACTER_MAXIMUM_LENGTH ,
 NUMERIC_PRECISION ,
 NUMERIC_PRECISION_RADIX ,
 NUMERIC_SCALE ,
 DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA , TABLE_NAME ,ORDINAL_POSITION
</code></pre>

<a href="https://i.stack.imgur.com/KXNxK.png" rel="noreferrer"><img src="https://i.stack.imgur.com/KXNxK.png" alt="enter image description here"></a>

blocks|key|414110|text|获取基本元数据摘要的最简单方法是使用临时表，然后使用EXEC函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|414111|SELECT+*+INTO+#TempTable+FROM+TableName
EXEC+[tempdb].[dbo].[sp_help]+N'#TempTable'|code-block|syntax|javascript|414112|对于表中的所有列，这将提供列名，|414113|数据类型，|414114|计算长度，|414115|Prec，|414116|Scale，可为Nullable，|414117|TrimTrailingBlanks、FixedLenNullInSource、排序规则类型|414118|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Easiest way to get basic metadata summary is to use a temp table and then use EXEC function:

<pre><code>SELECT * INTO #TempTable FROM TableName
EXEC [tempdb].[dbo].[sp_help] N'#TempTable'
</code></pre>

For all columns in the table, this will give you 
Column Name, 
Data Type, 
Computed Length, 
Prec, 
Scale, 
Nullable, 
TrimTrailingBlanks, 
FixedLenNullInSource, 
Collation Type

blocks|key|3255286|text|有两种简单的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3255287|3255288|选项1|unordered-list-item|3255289|3255290|sp_help+'schema.table_name'|3255291|3255292|选项2|3255293|3255294|SELECT+*+FROM+INFORMATION_SCHEMA.columns+c+WHERE+c.table_name+=+'table_name'|3255295|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|S|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|W|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Y|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|P|$]]

There are 2 simple ways:
<ul>
<li>Option 1
sp_help 'schema.table_name'
</li>
<li>Option 2
SELECT * FROM INFORMATION_SCHEMA.columns c WHERE c.table_name = 'table_name'
</li>
</ul>

I am trying to find a way to extract information about my tables in SQL Server (2008). 
The data I need needs to include the description of the table (filled from the Description property in the Properties Window), a list of fields of that table and their respective data types.

Is there any way I can extract such meta-data? I presume I have to use some <code>sys</code> sp but I'n not sure which one.

SQL Server: Extract Table Meta-Data (description, fields and their data types)

SQL Server

我正在尝试寻找一种方法来提取SQL Server (2008)中有关我的表的信息。我需要的数据需要包括表的描述(从属性窗口中的Description属性填充)、该表的字段的列表以及它们各自的数据类型。有没有办法提取这样的元数据？我想我必须使用一些sys sp，但我不确定是哪一个。

问SQL Server:提取表元数据(说明、字段及其数据类型)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:提取表元数据(说明、字段及其数据类型)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:提取表元数据(说明、字段及其数据类型)
EN