Shulou(Shulou.com)06/01 Report--

First, find out 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 ORDER BY tab.name

Find out all the field descriptions of a library

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

Find out all the field descriptions of a library (find out the detailed formatted version 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. Valuegramme') else''end, field serial number = a.colorder, field name = a.name, identity = case when COLUMNPROPERTY (a.iddepartment a.namememery IsIdentity') = 1 then' √ 'else' 'end Primary key = case when exists (SELECT 1 FROM sysobjects where xtype='PK' and name in (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, occupied bytes = a.length, length = COLUMNPROPERTY (a.idmema.name) 'PRECISION'), decimal places = isnull (COLUMNPROPERTY (a.idmema. Name else'), 0), allow null = case when a.isnullable=1 then '√' else''end, default = isnull (e.textjiao'), 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.xtypewritten properties U' and d.nameboards 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.