[转]方便查看数据表和字段的SQL语句(适用于SQLServer2000)-优快云博客

本文提供两个SQL查询语句，用于列出SQLServer2000中所有用户数据表及其字段信息。第一个语句展示如何获取所有用户数据表的名称；第二个语句则详细列出每个表的所有字段及其属性。

　　经过对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 说明
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' INNER JOIN
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
ORDER BY o.name, c.colid

转载于:https://www.cnblogs.com/macou/archive/2007/03/24/686245.html