SELECT
Table name = D.name,
Table Description = case when a.colorder=1 then IsNull (F.value, ') Else ' end,
Field ordinal = A.colorder,
Field name = A.name,
Identification = 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 Parent_obj=a.id and name in (
Select name from sysindexes WHERE indid in (SELECT indid from Sysindexkeys where id = a.id and colid=a.colid)) Then ' √ ' E LSE ' END,
Type = B.name,
Number of bytes occupied = a.length,
Length = ColumnProperty (a.id,a.name, ' PRECISION '),
Number of decimal digits = IsNull (ColumnProperty (a.id,a.name, ' scale '), 0),
Allow NULL = case time 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.xusertype=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
where
D.name= ' medical_record_diagnosis '--If you only query the specified table, plus this red where condition, TableName is the name of the table to query; Remove the red Where Condition query says table information
ORDER BY
A.id,a.colorder
fields and comments for SQL Server query tables