数据字典

SQL 2005 中数据字典查询

SELECT  
表名              =   CASE   WHEN   A.COLORDER=1   THEN   D.NAME   ELSE   ' '   END,
表说明          =   CASE   WHEN   A.COLORDER=1   THEN   ISNULL(F.VALUE, ' ')   ELSE   ' '   END,
字段序号      =   A.COLORDER,
字段名          =   A.NAME,
标识              =   CASE   WHEN   COLUMNPROPERTY(   A.ID,A.NAME, 'ISIDENTITY ')=1   THEN   '√'ELSE   ' '   END,
主键              =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   AND   NAME   IN   (
SELECT   NAME   FROM   SYSINDEXES   WHERE   INDID   IN(
SELECT   INDID   FROM   SYSINDEXKEYS   WHERE   ID   =   A.ID   AND   COLID=A.COLID)))   THEN   '√'   ELSE   ' '   END,
类型              =   B.NAME,
占用字节数 =   A.LENGTH,
长度              =   COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '),
小数位数      =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0),
允许空          =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√'ELSE   ' '   END,
默认值          =   ISNULL(E.TEXT, ' '),
字段说明      =   ISNULL(G.[VALUE], ' ')
FROM  
SYSCOLUMNS   A
LEFT   JOIN  
SYSTYPES   B  
ON  
A.XUSERTYPE=B.XUSERTYPE
INNER   JOIN  
SYSOBJECTS   D  
ON  
A.ID=D.ID     AND   D.XTYPE= 'U '   AND     D.NAME <> 'DTPROPERTIES '
LEFT   JOIN  
SYSCOMMENTS   E  
ON  
A.CDEFAULT=E.ID
LEFT   JOIN  
sys.extended_properties   G  
ON  
A.ID=G.major_id   AND   A.COLID=G.minor_id    
LEFT   JOIN  
sys.extended_properties   F  
ON  
D.ID=F.major_id   AND   F.minor_id=0
ORDER   BY  
A.ID,A.COLORDER

-----------------------------------------【存储过程，主，外键 ，触发器等信息】--------------------------------

SELECT DISTINCT
      TOP 100 PERCENT o.xtype,
      CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN
       '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN'
       THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程'
       END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,
      c.text AS 声明语句
FROM dbo.sysobjects o LEFT OUTER JOIN
      dbo.syscomments c ON o.id = c.id
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND
      (OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)
ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN
       'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图'
       WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值'
       ELSE '存储过程' END DESC

 

-----------------------------------------【索引】--------------------------------

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