T-SQL 取得資料表的 schema

如果想透過 T-SQL 來取得資料庫中的某個資料表的 schema，可以透過以下語法：

--宣告所要查詢的資料表名稱
declare @tbName nvarchar(200)
set @tbName='close_base'

Select a.colid, a.name,
 c.name + '(' + Convert(varchar(4), (Case When a.xtype In (99, 231, 239) Then a.length / 2 Else a.length End)) + ')' col_type, 
 (Case When b.pk_name Is Not Null Then 'PK' Else 
  (Case When a.isnullable = 0 Then 'NN' Else 'N' End) End) col_status,
 IsNull(e.text, '') col_default
From sysobjects main
Inner Join syscolumns a
 On main.id = a.id
Left Join (
  Select a.id, c.name pk_name
  From sysindexes a
  Inner Join sysindexkeys b
   On a.id = b.id And a.indid = b.indid
  Inner Join syscolumns c
   On a.id = c.id And b.colid = c.colid
  Where a.status & 2048 = 2048) b
 On a.id = b.id And a.name = b.pk_name
Inner Join systypes c
 On a.xtype = c.xtype And c.status = 0 
 --and c.name<>'image' --可在此過濾欄位格式
Left Join sys.extended_properties d
 On a.id = d.major_id And a.colid = d.minor_id 
 And d.name = 'chnName'
Left Join dbo.syscomments e
 On a.cdefault = e.id
Where main.name = @tbName
order by a.colid

就可以得到所要的 schema 內容了。