經過對SQLServer2000系統表的分析,寫出了以下兩個SQL語句。可以把這兩個語句分别建為兩個“視圖”,友善檢視使用者資料表和字段的資訊。
1、列出所有的使用者資料表:
SELECT TOP 100 PERCENT o.name AS 表名
FROM dbo.syscolumns c INNER JOIN
dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
o.name <> 'dtproperties' LEFT OUTER JOIN
dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
WHERE (c.colid = 1)
ORDER BY o.name, c.colid
2、列出所有的使用者資料表及其字段資訊:
SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,
t.name AS 類型, c.length AS 長度, c.isnullable AS 允許空,
CAST(m.[value] AS Varchar(100)) AS 說明
o.name <> 'dtproperties' INNER JOIN
dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
你如果有更好的方法,請交流一下:)