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 Ordinal =a.colorder,
Field name =a.name,
Identify =case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else ' end,
Primary key =case when exists (SELECT 1 to 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 empty =case when 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.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
--where d.name= ' table to query '--if only the specified table is queried, plus this condition
ORDER BY A.id,a.colorder