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

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

 

[sql] view plaincopyprint?
  1. SELECT TOP 100 PERCENT   
  2.        CASE   
  3.             WHEN a.colorder = 1 THEN d.name  
  4.             ELSE ''  
  5.        END AS 表名,  
  6.        CASE   
  7.             WHEN a.colorder = 1 THEN ISNULL(f.value, '')  
  8.             ELSE ''  
  9.        END AS 表说明,  
  10.        a.colorder AS 字段序号,  
  11.        a.name AS 字段名,  
  12.        CASE   
  13.             WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'  
  14.             ELSE ''  
  15.        END AS 标识,  
  16.        CASE   
  17.             WHEN EXISTS  
  18.                  (  
  19.                      SELECT 1  
  20.                      FROM   dbo.sysindexes si  
  21.                             INNER JOIN dbo.sysindexkeys sik  
  22.                                  ON  si.id = sik.id  
  23.                                  AND si.indid = sik.indid  
  24.                             INNER JOIN dbo.syscolumns sc  
  25.                                  ON  sc.id = sik.id  
  26.                                  AND sc.colid = sik.colid  
  27.                             INNER JOIN dbo.sysobjects so  
  28.                                  ON  so.name = si.name  
  29.                                  AND so.xtype = 'PK'  
  30.                      WHERE  sc.id = a.id  
  31.                             AND sc.colid = a.colid  
  32.                  ) THEN '√'  
  33.             ELSE ''  
  34.        END AS 主键,  
  35.        b.name AS 类型,  
  36.        a.length AS 长度,  
  37.        COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,  
  38.        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,  
  39.        CASE   
  40.             WHEN a.isnullable = 1 THEN '√'  
  41.             ELSE ''  
  42.        END AS 允许空,  
  43.        ISNULL(e.text, '') AS 默认值,  
  44.        ISNULL(g.[value], '') AS 字段说明,  
  45.        d.crdate AS 创建时间,  
  46.        CASE   
  47.             WHEN a.colorder = 1 THEN d.refdate  
  48.             ELSE NULL  
  49.        END AS 更改时间  
  50. FROM   dbo.syscolumns a  
  51.        LEFT OUTER JOIN dbo.systypes b  
  52.             ON  a.xtype = b.xusertype  
  53.        INNER JOIN dbo.sysobjects d  
  54.             ON  a.id = d.id  
  55.             AND d.xtype = 'U'   --视图为'V'   
  56.             AND d.status >= 0  
  57.        LEFT OUTER JOIN dbo.syscomments e  
  58.             ON  a.cdefault = e.id  
  59.        LEFT OUTER JOIN dbo.sysproperties g  
  60.             ON  a.id = g.id  
  61.             AND a.colid = g.smallid  
  62.             AND g.name = 'MS_Description'  
  63.        LEFT OUTER JOIN dbo.sysproperties f  
  64.             ON  d.id = f.id  
  65.             AND f.smallid = 0  
  66.             AND f.name = 'MS_Description'  
  67. ORDER BY  
  68.        d.name,  
  69.        a.colorder  
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.查询数据库下所有存储过程的结构信息（包括存储过程名、参数名、类型等等，包括无参的存储过程）

 

[sql] view plaincopyprint?
  1. SELECT TOP 100 PERCENT   
  2.        CASE   
  3.             WHEN a.colorder = 1 THEN d.name  
  4.             WHEN ISNULL(a.colorder, 0) = 0 THEN d.name  
  5.             ELSE ''  
  6.        END AS 存储过程名称,  
  7.        CASE   
  8.             WHEN a.colorder = 1 THEN ISNULL(f.value, '')  
  9.             ELSE ''  
  10.        END AS 存储过程说明,  
  11.        a.colorder AS 参数序号,  
  12.        a.name AS 参数名称,  
  13.        b.name AS 类型,  
  14.        a.length AS 长度,  
  15.        COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,  
  16.        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,  
  17.        CASE   
  18.             WHEN a.isoutparam = 1 THEN '√'  
  19.             ELSE ''  
  20.        END AS 参数输出,  
  21.        ISNULL(g.[value], '') AS 参数说明,  
  22.        d.crdate AS 创建时间,  
  23.        CASE   
  24.             WHEN a.colorder = 1 THEN d.refdate  
  25.             ELSE NULL  
  26.        END AS 更改时间  
  27. FROM   dbo.syscolumns a  
  28.        LEFT OUTER JOIN dbo.systypes b  
  29.             ON  a.xtype = b.xusertype  
  30.        RIGHT OUTER JOIN dbo.sysobjects d  
  31.             ON  a.id = d.id  
  32.        LEFT OUTER JOIN dbo.sysproperties g  
  33.             ON  a.id = g.id  
  34.             AND a.colid = g.smallid  
  35.             AND g.name = 'MS_Description'  
  36.        LEFT OUTER JOIN dbo.sysproperties f  
  37.             ON  d.id = f.id  
  38.             AND f.smallid = 0  
  39.             AND f.name = 'MS_Description'  
  40. WHERE  d.xtype = 'P'  
  41.        AND d.status >= 0  
  42. ORDER BY  
  43.        d.name,  
  44.        a.colorder  
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 >= 0
ORDER BY
       d.name,
       a.colorder
截图如下：

 

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

 

[sql] view plaincopyprint?
  1. SELECT TOP 100 PERCENT   
  2.        CASE   
  3.             WHEN a.usertype IS NULL THEN d.name  
  4.             WHEN (  
  5.                      NOT EXISTS(  
  6.                          SELECT c.usertype  
  7.                          FROM   syscolumns c  
  8.                          WHERE  a.id = c.id  
  9.                                 AND c.usertype IS NULL  
  10.                      )  
  11.                  ) AND (a.colorder = 1 AND a.number = 0) THEN d.name  
  12.             ELSE ''  
  13.        END AS 函数名称,  
  14.        CASE   
  15.             WHEN a.colorder = 0 THEN ISNULL(f.value, '')  
  16.             ELSE ''  
  17.        END AS 函数说明,  
  18.        a.colorder AS 参数序号,  
  19.        a.name AS 参数名称,  
  20.        b.name AS 类型,  
  21.        a.length AS 长度,  
  22.        COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,  
  23.        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,  
  24.        CASE   
  25.             WHEN a.usertype IS NULL THEN '√'  
  26.             WHEN (  
  27.                      NOT EXISTS(  
  28.                          SELECT c.usertype  
  29.                          FROM   syscolumns c  
  30.                          WHERE  a.id = c.id  
  31.                                 AND c.usertype IS NULL  
  32.                      )  
  33.                  ) AND a.number = 0 THEN '√'  
  34.             ELSE ''  
  35.        END AS 返回值,  
  36.        ISNULL(g.[value], '') AS 参数说明,  
  37.        d.crdate AS 创建时间,  
  38.        CASE   
  39.             WHEN a.usertype IS NULL THEN d.refdate  
  40.             WHEN (  
  41.                      NOT EXISTS(  
  42.                          SELECT c.usertype  
  43.                          FROM   syscolumns c  
  44.                          WHERE  a.id = c.id  
  45.                                 AND c.usertype IS NULL  
  46.                      )  
  47.                  ) AND (a.colorder = 1 AND a.number = 0) THEN d.refdate  
  48.             ELSE NULL  
  49.        END AS 更改时间  
  50. FROM   dbo.syscolumns a  
  51.        LEFT OUTER JOIN dbo.systypes b  
  52.             ON  a.xtype = b.xusertype  
  53.        INNER JOIN dbo.sysobjects d  
  54.             ON  a.id = d.id  
  55.             AND (d.xtype = 'FN' OR d.xtype = 'IF' OR d.xtype = 'TF')  
  56.             AND d.status >= 0  
  57.        LEFT OUTER JOIN dbo.sysproperties g  
  58.             ON  a.id = g.id  
  59.             AND a.colid = g.smallid  
  60.             AND g.name = 'MS_Description'  
  61.        LEFT OUTER JOIN dbo.sysproperties f  
  62.             ON  d.id = f.id  
  63.             AND f.smallid = 0  
  64.             AND f.name = 'MS_Description'  
  65. ORDER BY  
  66.        d.name,  
  67.        a.number,  
  68.        a.colorder         
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
截图如下：

 

 

转载自http://blog.csdn.net/akof1314/article/details/6643027

posted @ 2015-06-23 22:49  水曰  阅读(126)  评论(0)    收藏  举报