First, identify all the table names of a library
use database_namegoselect tab.name as Tablenamefrom sys.columns col inner join sys.tables tab on col.object_id = tab.object_id left join sys.extended_properties per ON col.column_id = per.minor_id AND per.major_id = tab.object_id INNER JOIN sys.types type on col.user_type_id = type.user_ Type_idgroup by tab.name&nbsP;order by tab.name
Use database_namegoselect tab.name as tablename, col.name AS Column_name, per.value as column_comment, col.is_identity AS column_key, type.name as data_type, col.max_length as column_lengthfrom sys.columns col INNER JOIN sys.tables tab on col.object_id = tab.object _id left join sys.extended_properties per on col.column_id = per.minor_id and per.major_id = tab.object_id inner join sys.types type on col.user_ type_id = type.user_type_idorder by tab.name, col.is_identity desc
II. Find out all the field descriptions for a library (find out the detailed format of the data)
use database_namegoselect Table Name =case when a.colorder=1 then d.name else ' end, Table Description =case when a.colorder=1 then isnull ( F.value, ') else ' end, Field ordinal =a.colorder, field name =a.name, logo =case when columnproperty ( a.id,a.name, ' isidentity ') =1 then ' √ ' else ' end, primary Key =case when exists (select 1 from sysobjects where xtype= ' PK ' and name in ( &nBsp; select name from sysindexes where indid in ( select indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid )) then ' √ ' else ' end, type = b.name, the number of bytes occupied =a.length, length =columnproperty (a.id,a.name, ' PRECISION '), decimal =isnull (columnproperty (A.id,a.name, ' Scale '), 0), allow null =case when a.isnullable=1 then ' √ 'else ' end, default value =isnull ( E.text, '), field Description =isnull (G.[value], ") FROM syscolumns a left join systypes b on a.xtype =b.xusertype inner join Sysobjects d on a.id=d.id and d.xtype= ' U ' and d.name<> ' Dtproperties ' left join Syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id left join sys.extended_properties f on D.id=f.major_id and f.minor_id=0 order by a.id,a.colorder
This article is from the "Wind Trace _ Snow Tiger" blog, please be sure to keep this source http://snowtiger.blog.51cto.com/12931578/1929110
Methods for checking SQL Server table structure description