Select Table name = D. Name, -- case when a. colorder = 1 then D. Name else ''end, FIELD No. = A. colorder, Field name = A. Name, Id = 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 ( 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, Bytes occupied = A. length, Length = columnproperty (A. ID, A. Name, 'precision '), Decimal places = 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 Left join policypes 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 sysproperties g on A. ID = G. ID and A. colid = G. smallid Order by A. ID, A. colorder This is reproduced on the Internet. (Http://www.alixixi.com/Dev/DB/MSSQL/2007/2007020916013.html) you can use the preceding statement to create a view, in this way, when a table is added to the database, accurate structure information can be obtained in a timely manner. This view is especially useful when many tables are encountered during development, and the Program requires dynamic display of results. For example, to dynamically display different table information on a page, you can use the content in this view to output the name of the column to be displayed, the Chinese name (field description can be used, this requires you to enter the description when creating the table ). In this way, the program will write much less Code , and you do not need to modify many pages when you want to adjust the page. |