Display the table structure in the database (new index and table description information)

Select
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 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], ''),
Index name = isnull (H. Index name ,''),
Index order = isnull (H. Sort ,'')
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
Left join sysproperties F on D. id = f. ID and F. smallid = 0
Left join (-- this part is the index information. To display the correspondence between indexes and tables and fields
Select index name = A. Name, C. ID, D. colid
, Sort = case indexkey_property (C. ID, B. indid, B. keyno, 'isdesending ')
When 1 then 'descending order 'when 0 then' Ascending Order 'end
From sysindexes
Join sysindexkeys B on A. ID = B. ID and A. indid = B. indid
Join (-- the role here is the one with the smallest index number when multiple indexes exist.
Select ID, colid, indid = min (indid) from sysindexkeys
Group by ID, colid) B1 on B. ID = b1.id and B. colid = b1.colid and B. indid = b1.indid
Join sysobjects C on B. ID = C. ID and C. xtype = 'U' and C. Name <> 'dtproperties'
Join syscolumns D on B. ID = D. id and B. colid = D. colid
Where a. indid not in (0,255)
) H on A. ID = H. ID and A. colid = H. colid
-- Where D. Name = 'table to be query' -- this condition is added if only the specified table is queried.
Order by A. ID, A. colorder