MSSQL2000 获取数据库表、视图、存储过程等结构信息

1.查询数据库下所有用户表（或视图）的结构信息（包括表名、字段名、类型等等）

SELECT TOP 100 PERCENT        CASE             WHEN a.colorder = 1 THEN d.name            ELSE ''       END AS 表名,       CASE             WHEN a.colorder = 1 THEN ISNULL(f.value, '')            ELSE ''       END AS 表说明,       a.colorder AS 字段序号,       a.name AS 字段名,       CASE             WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'            ELSE ''       END AS 标识,       CASE             WHEN EXISTS                 (                     SELECT 1                     FROM   dbo.sysindexes si                            INNER JOIN dbo.sysindexkeys sik                                 ON  si.id = sik.id                                 AND si.indid = sik.indid                            INNER JOIN dbo.syscolumns sc                                 ON  sc.id = sik.id                                 AND sc.colid = sik.colid                            INNER JOIN dbo.sysobjects so                                 ON  so.name = si.name                                 AND so.xtype = 'PK'                     WHERE  sc.id = a.id                            AND sc.colid = a.colid                 ) THEN '√'            ELSE ''       END AS 主键,       b.name AS 类型,       a.length AS 长度,       COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,       ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,       CASE             WHEN a.isnullable = 1 THEN '√'            ELSE ''       END AS 允许空,       ISNULL(e.text, '') AS 默认值,       ISNULL(g.[value], '') AS 字段说明,       d.crdate AS 创建时间,       CASE             WHEN a.colorder = 1 THEN d.refdate            ELSE NULL       END AS 更改时间FROM   dbo.syscolumns a       LEFT OUTER JOIN dbo.systypes b            ON  a.xtype = b.xusertype       INNER JOIN dbo.sysobjects d            ON  a.id = d.id            AND d.xtype = 'U'   --视图为'V'            AND d.status >= 0       LEFT OUTER JOIN dbo.syscomments e            ON  a.cdefault = e.id       LEFT OUTER JOIN dbo.sysproperties g            ON  a.id = g.id            AND a.colid = g.smallid            AND g.name = 'MS_Description'       LEFT OUTER JOIN dbo.sysproperties f            ON  d.id = f.id            AND f.smallid = 0            AND f.name = 'MS_Description'ORDER BY       d.name,       a.colorder

截图如下：

2.查询数据库下所有存储过程的结构信息（包括存储过程名、参数名、类型等等，包括无参的存储过程）

SELECT TOP 100 PERCENT        CASE             WHEN a.colorder = 1 THEN d.name            WHEN ISNULL(a.colorder, 0) = 0 THEN d.name            ELSE ''       END AS 存储过程名称,       CASE             WHEN a.colorder = 1 THEN ISNULL(f.value, '')            ELSE ''       END AS 存储过程说明,       a.colorder AS 参数序号,       a.name AS 参数名称,       b.name AS 类型,       a.length AS 长度,       COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,       ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,       CASE             WHEN a.isoutparam = 1 THEN '√'            ELSE ''       END AS 参数输出,       ISNULL(g.[value], '') AS 参数说明,       d.crdate AS 创建时间,       CASE             WHEN a.colorder = 1 THEN d.refdate            ELSE NULL       END AS 更改时间FROM   dbo.syscolumns a       LEFT OUTER JOIN dbo.systypes b            ON  a.xtype = b.xusertype       RIGHT OUTER JOIN dbo.sysobjects d            ON  a.id = d.id       LEFT OUTER JOIN dbo.sysproperties g            ON  a.id = g.id            AND a.colid = g.smallid            AND g.name = 'MS_Description'       LEFT OUTER JOIN dbo.sysproperties f            ON  d.id = f.id            AND f.smallid = 0            AND f.name = 'MS_Description'WHERE  d.xtype = 'P'       AND d.status >= 0ORDER BY       d.name,       a.colorder

截图如下：

3.查询数据库下所有用户自定义函数的结构信息（包括函数名、参数名、类型、返回值等等）

SELECT TOP 100 PERCENT        CASE             WHEN a.usertype IS NULL THEN d.name            WHEN (                     NOT EXISTS(                         SELECT c.usertype                         FROM   syscolumns c                         WHERE  a.id = c.id                                AND c.usertype IS NULL                     )                 ) AND (a.colorder = 1 AND a.number = 0) THEN d.name            ELSE ''       END AS 函数名称,       CASE             WHEN a.colorder = 0 THEN ISNULL(f.value, '')            ELSE ''       END AS 函数说明,       a.colorder AS 参数序号,       a.name AS 参数名称,       b.name AS 类型,       a.length AS 长度,       COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,       ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,       CASE             WHEN a.usertype IS NULL THEN '√'            WHEN (                     NOT EXISTS(                         SELECT c.usertype                         FROM   syscolumns c                         WHERE  a.id = c.id                                AND c.usertype IS NULL                     )                 ) AND a.number = 0 THEN '√'            ELSE ''       END AS 返回值,       ISNULL(g.[value], '') AS 参数说明,       d.crdate AS 创建时间,       CASE             WHEN a.usertype IS NULL THEN d.refdate            WHEN (                     NOT EXISTS(                         SELECT c.usertype                         FROM   syscolumns c                         WHERE  a.id = c.id                                AND c.usertype IS NULL                     )                 ) AND (a.colorder = 1 AND a.number = 0) THEN d.refdate            ELSE NULL       END AS 更改时间FROM   dbo.syscolumns a       LEFT OUTER JOIN dbo.systypes b            ON  a.xtype = b.xusertype       INNER JOIN dbo.sysobjects d            ON  a.id = d.id            AND (d.xtype = 'FN' OR d.xtype = 'IF' OR d.xtype = 'TF')            AND d.status >= 0       LEFT OUTER JOIN dbo.sysproperties g            ON  a.id = g.id            AND a.colid = g.smallid            AND g.name = 'MS_Description'       LEFT OUTER JOIN dbo.sysproperties f            ON  d.id = f.id            AND f.smallid = 0            AND f.name = 'MS_Description'ORDER BY       d.name,       a.number,       a.colorder       

截图如下：