SELECT
Table name = Case if A.colorder=1 then D.name Else ' End,
Table Description = case when a.colorder=1 then IsNull (F.value, ') Else ' End,
Field ordinal = A.colorder,
Field name = A.name,
Field Description = IsNull (G.[value], "),
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, ")
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= ' OrderInfo '--If you only query the specified table, add this condition
Order by
A.id,a.colorder
MS SQL Server Export table structure to excel