［数据库字典］简单的3个SQL视图搞定所有SqlServer数据库字典

原文:https://www.cnblogs.com/qiantuwuliang/archive/2009/06/06/1497372.html

(2007-11-29更新）网上有很多SQL SERVER数据库字典的SQL语句，七零八落，我在工作整理了一下思路，总结SQL代码如下。数据库字典包括表结构(分2K和2005）、索引和主键.外键.约束.视图.函数.存储过程.触发器.规则。可以在企业管理器、查询分析器中简单执行，直接了当的查出SQL2K及SQL2005的所有数据字典，方便文档的编写，希望对大家有帮助。

1. SqlServer2000数据库字典--表结构.sql

SELECT  TOP  100  PERCENT  -- a.id, 
       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 '  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

    SqlServer2005数据库字典--表结构.sql

SELECT  TOP  100  PERCENT  -- a.id, 
       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 '  AND 
      d.status  >=  0  LEFT  OUTER  JOIN
      dbo.syscomments e  ON a.cdefault  = e.id  LEFT  OUTER  JOIN
      sys.extended_properties g  ON a.id  = g.major_id  AND a.colid  = g.minor_id  AND 
      g.name  =  ' MS_Description '  LEFT  OUTER  JOIN
      sys.extended_properties f  ON d.id  = f.major_id  AND f.minor_id  =  0  AND 
      f.name  =  ' MS_Description '
ORDER  BY d.name, 字段序号

2.  SqlServer数据库字典--索引.sql

SELECT  TOP  100  PERCENT  -- a.id, 
       CASE  WHEN b.keyno  =  1  THEN c.name  ELSE  ''  END  AS 表名, 
       CASE  WHEN b.keyno  =  1  THEN a.name  ELSE  ''  END  AS 索引名称, d.name  AS 列名, 
      b.keyno  AS 索引顺序,  CASE indexkey_property(c.id, b.indid, b.keyno,  ' isdescending ') 
       WHEN  1  THEN  ' 降序 '  WHEN  0  THEN  ' 升序 '  END  AS 排序,  CASE  WHEN p.id  IS  NULL 
       THEN  ''  ELSE  ' √ '  END  AS 主键,  CASE  INDEXPROPERTY(c.id, a.name,  ' IsClustered ') 
       WHEN  1  THEN  ' √ '  WHEN  0  THEN  ''  END  AS 聚集,  CASE  INDEXPROPERTY(c.id, 
      a.name,  ' IsUnique ')  WHEN  1  THEN  ' √ '  WHEN  0  THEN  ''  END  AS 唯一, 
       CASE  WHEN e.id  IS  NULL  THEN  ''  ELSE  ' √ '  END  AS 唯一约束, 
      a.OrigFillFactor  AS 填充因子, c.crdate  AS 创建时间, c.refdate  AS 更改时间
FROM dbo.sysindexes a  INNER  JOIN
      dbo.sysindexkeys b  ON a.id  = b.id  AND a.indid  = b.indid  INNER  JOIN
      dbo.syscolumns d  ON b.id  = d.id  AND b.colid  = d.colid  INNER  JOIN
      dbo.sysobjects c  ON a.id  = c.id  AND c.xtype  =  ' U '  LEFT  OUTER  JOIN
      dbo.sysobjects e  ON e.name  = a.name  AND e.xtype  =  ' UQ '  LEFT  OUTER  JOIN
      dbo.sysobjects p  ON p.name  = a.name  AND p.xtype  =  ' PK '
WHERE ( OBJECTPROPERTY(a.id, N ' IsUserTable ')  =  1)  AND ( OBJECTPROPERTY(a.id, 
      N ' IsMSShipped ')  =  0)  AND ( INDEXPROPERTY(a.id, a.name,  ' IsAutoStatistics ')  =  0)
ORDER  BY c.name, a.name, b.keyno

3.  SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则.sql

SELECT  DISTINCT 
       TOP  100  PERCENT  isnull(p.name, '')  AS 父对象, o.xtype, 
       CASE o.xtype  WHEN  ' C '  THEN  ' CHECK 约束 '  WHEN  ' D '  THEN  ' 默认值或DEFAULT约束 '
        WHEN  ' F '  THEN  ' FOREIGNKEY约束 '  WHEN  ' L '  THEN  ' 日志 '  WHEN  ' FN '  THEN  ' 标量函数 '
        WHEN  ' IF '  THEN  ' 内嵌表函数 '  WHEN  ' P '  THEN  ' 存储过程 '  WHEN  ' PK '  THEN  ' PRIMARYKEY约束 '
        WHEN  ' RF '  THEN  ' 复制筛选存储过程 '  WHEN  ' S '  THEN  ' 系统表 '  WHEN  ' TF '  THEN  ' 表函数 '
        WHEN  ' TR '  THEN  ' 触发器 '  WHEN  ' U '  THEN  ' 用户表 '  WHEN  ' UQ '  THEN  ' UNIQUE 约束 '
        WHEN  ' V '  THEN  ' 视图 '  WHEN  ' X '  THEN  ' 扩展存储过程 '  WHEN  ' R '  THEN  ' 规则 '  ELSE  NULL
        END  AS 类型, o.name  AS 对象名, o.crdate  AS 创建时间, o.refdate  AS 更改时间, 
      c. text  AS 声明语句, OBJECTPROPERTY(o.id, N ' IsMSShipped ')
FROM dbo.sysobjects o  Left  JOIN
      dbo.sysobjects p  ON o.parent_obj  = p.id  LEFT  OUTER  JOIN
      dbo.syscomments c  ON o.id  = c.id
WHERE  -- (o.xtype IN ('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V')) AND 
      ( OBJECTPROPERTY(o.id, N ' IsMSShipped ')  =  0)  AND ( isnull(p.name, '')  <> N ' dtproperties ')
ORDER  BY o.xtype  DESC