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 serial number = a.colorder,
Field name = a.Name,
Logo = Case When ColumnProperty (A.ID, A.NAME, 'ISIDENTITY') = 1 TEN '√'lse' '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 color = a.colid
))) The '√' else '' end,
Type = B.Name,
Number of bytes = a.length,
Length = ColumnProperty (A.ID, A.Name, 'Precision'),
Digital digits = ISNULL (ColumnProperty (A.ID, A.NAME, 'Scale'), 0),
Allow empty = Case When a.isnullable = 1 Then '√'lse' 'End,
Default = Isnull (E.Text, ''),
Field Description = ISNULL (g. [Value], ''),
Index Name = Isnull (h. Index Name, ''),
Index order = isnull (h. Sort, '')
From syscolumns a
Left join systemypes 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 index information, if you want to display the corresponding relationship between the index and the table and field, you can
SELECT index name = a.Name, C.ID, D.COLID
, Sort = Case IndexKey_Property (C.ID, B.indid, B.Keyno, 'Isdescending')
When 1 Then 'Descendment' When 0 Then 'Skims' End
From sysindexes a
Join sysindexkeys b on A.Id = B.ID and A.indid = B.indID
Join (- The role here is when there are multiple indexes, the solemn
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.indidJoin 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 = 'To query table' - If you only query the specified table, add this condition
Order by a.id, a.colorder
