快速取得資料庫資料表結構

--適用SQL 2005，簡單列一下

select t.name as [table name], c.name AS [column name], tp.name AS [資料型別],
case tp.name
when 'bigint' then cast(c.precision as varchar(3))
when 'int' then cast(c.precision as varchar(3))
when 'smallint' then cast(c.precision as varchar(3))
when 'tinyint' then cast(c.precision as varchar(3))
when 'numeric' then '(' + cast(c.precision as varchar(3)) + ',' + cast(c.scale as varchar(3)) + ')'
when 'decimal' then '(' + cast(c.precision as varchar(3)) + ',' + cast(c.scale as varchar(3)) + ')'
when 'nvarchar' then cast((c.max_length/2) as varchar(5))
when 'nchar' then cast((c.max_length/2) as varchar(5))
else cast(c.max_length as varchar(5))
end as [長度],
CASE WHEN c.is_nullable=1 THEN 'ok' ELSE 'no' END AS [NULL ok],
ep.value as [描述]
from sys.tables as t
inner join sys.columns as c on t.object_id=c.object_id
inner join sys.types as tp on c.system_type_id = tp.system_type_id
left outer join sys.extended_properties as ep on (ep.major_id = t.object_id
and ep.minor_id = c.column_id and ep.class = 1 )
where tp.system_type_id = tp.user_type_id
order by t.name,c.name

--適用SQL 2000，我從對岸看到的，給他貼過來，對岸高手好厲害

SELECT sysobjects.name AS [table], sysproperties.[value] AS 表說明,
syscolumns.name AS field, properties.[value] AS '欄位說明', systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
'Scale'), 0) AS 小數位數 , syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 標識, 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 = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS 主鍵 FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')