顯示MS-SQL表結構,包括表和列的註釋

Select 表名 = Case When a.Colorder=1 Then d.Name Else '' End,
表說明 = Case When a.Colorder=1 Then Isnull(f.Value,'') Else '' End,
欄位序號 = a.Colorder,
欄位名 = a.Name,
標識 = Case When ColumnProperty(a.ID,a.Name,'IsIdentity')=1 Then '√' Else '' End,
主鍵 = 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,
型別 = b.Name,
佔用位元組數 = a.Length,
長度 = ColumnProperty(a.ID,a.Name,'Precision'),
小數位數 = Isnull(ColumnProperty(a.ID,a.Name,'Scale'),0),
允許空 = Case When a.IsNullable = 1 then '√' else '' end,
預設值 = Isnull(e.Text,''),
欄位說明 = 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 Sysproperties f on d.ID = f.ID And f.Smallid=0
left join Sysproperties g on a.ID = g.ID And a.Colid = g.Smallid
--Where d.Name='要查詢的表'--如果只查詢指定表,加上此條件
Where
Order By a.ID,a.Colorder

[@more@]