列出所有 Table 的資料結構.

/* 列出所有 Table 的資料結構.sql */
select C.name as TableName,
case A.status
   when '16' then 'PK'
   else ''
end as [Key], A.name as FieldName, '' as [Desc], D.name as type, A.length,
case A.isnullable
   when '1' then 'V'
   else ''
end as isnullable, isnull(B.text, '') as [Default],
case C.type
  when 'U' then 'Table'
  when 'V' then 'View'
  else ''
end as TableOrView
from syscolumns as A, syscomments as B, systypes as D, sysobjects as C
 where A.cdefault = B.id and A.xtype = D.xtype and D.status <> '1'  -- 排除sysname 和 nvarchar 同 xtype = 231
 and A.id = C.id
 and (C.type = 'U' or C.type = 'V')
order by C.name, A.colid

轉貼至 http://tw.myblog.yahoo.com/jw!nowr4YGVQE4pAgs1INa7JnTZmg--/article?mid=509