查看SqlServer数据库字典

/*
功能:            列出数据库字典.
创建时间:        2007-11-10
引用:            网络.
*/
------SqlServer数据库字典--表结构 for 2000
SELECT TOP 100 PERCENT --a.id, 
    表名       = 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],''),
    创建时间   = d.crdate,
    更改时间   = CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END
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 LEFT OUTER JOIN
      dbo.sysproperties f ON d.id = f.id AND f.smallid = 0
      --where d.name='T_SYS_USER'    --如果只查询指定表,加上此条件
ORDER BY d.name, a.colorder


----SqlServer数据库字典--表结构. for 2005
SELECT TOP 100 PERCENT --a.id, 
    表名       = 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],''),
    创建时间   = d.crdate,
    更改时间   = CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END
FROM dbo.syscolumns AS a LEFT OUTER JOIN
dbo.systypes AS b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN
dbo.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0
--where d.name='T_SYS_USER'    --如果只查询指定表,加上此条件
ORDER BY d.name, 字段序号

---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

--SqlServer2005 OBJECT_DEFINITION()函数,不需要格式化代码.
SELECT [Name],CASE 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 类型,OBJECT_DEFINITION(id)
FROM sysobjects
WHERE xtype IN ('C','D','P','FN','R','RF','TR','IF','TF','V')
ORDER BY xtype