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

(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


数据库技术就是一坛陈年老酒,越久越香,学以致用。

posted on 2007-11-09 11:13 简单 阅读(4669) 评论(32)  编辑 收藏 网摘 所属分类: SQLSERVER.Net开发

评论

 221.226.124.*         

有帮助!记下了,多谢!
2007-11-09 11:52 |

            

整理的好,学习先
2007-11-09 12:08 |  

            

用infomation schema系统视图就可以
2007-11-09 12:10 |  

            

不错。楼主辛苦了、
值得学习、参考。
2007-11-09 12:21 |  

 203.187.164.*         

第三个 有问题:

报错信息:

Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8110, which is greater than the allowable maximum of 8094.
2007-11-09 12:33 |

 [楼主]           

在企业管理器或查询分析器中应该都可以成功执行,代码没有问题。
5楼的错误信息在于字典数据太多,超过8094条记录的排序限制,可以取消排序代码再试一下:
SELECT DISTINCT
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)
2007-11-09 13:26 |  

            

不错 学习了。。。
2007-11-09 14:13 |  

 220.164.103.*         

记下了,谢谢!
2007-11-09 15:11 |

            

在2005中第一个个不能成功
对象名 'dbo.sysproperties' 无效。
2007-11-09 15:18 |  

            

经在sql server 2005上测试,
只要将一个SQL的最后两行替换如下,即可正确运行.
dbo.dtproperties g ON a.id = g.id AND a.colid = g.objectid LEFT OUTER JOIN

dbo.dtproperties f ON d.id = f.id AND f.objectid = 0
2007-11-09 15:27 |  

            

呵呵,不错收藏备用
2007-11-09 15:54 |  

 [楼主]           

感谢大家的捧场,dbo.sysproperties和dbo.dtproperties两个对象在SQL2005中都不存在,10楼兄台真测试通过吗?
SQL2005中的sys.extended_properties对象可以得到表和字段的说明(描述)。

可以用下面的SQL来在SQL2005中查询表字典,已测试通过:
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 = so.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 LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
ORDER BY d.name, a.colorder
2007-11-09 16:03 |  

            

好东西

懒人的福音
2007-11-09 17:35 |  

            

我记得我写了一个回复了,怎么找不到了。
2007-11-09 21:37 |  

            

有用!
2007-11-10 08:47 |  

            

收了,谢谢博主.
2007-11-12 18:19 |  

 222.68.147.*         

精典之作,感激不尽!
2007-11-13 10:25 |

 [楼主]           

很高兴能得到大家的支持和肯定。

今天发现并修正了“表结构.sql”的部分错误,正文已修改。
1.
dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'
改为
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'

2.
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
改为
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'

3.
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
改为
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'
2007-11-14 10:51 |  

 124.243.195.*         

请教大家个问题,数据库字段名和表名全都设计成大写和全是小写在执行的时候,性能有差别吗?因为我们公司有人说吧数据库字段全设计成大写会提高执行速度,是这样的吗?
2007-11-15 17:49 |

 [楼主]           

答复19楼兄台的问题,
SQL是否区别大小写与SQL排序规则有关,可以在数据库实例、数据库、用户对象三个层次上设置SQL排序规则,后者覆盖前者的设置,SQL排序规则字符串中CI 指定不区分大小写,CS 指定区分大小写。所以我们通常的设置是不区分大小写,不会影响执行速度。
2007-11-29 15:11 |  

 [楼主]           

今天更新了主贴的内容“3.SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则.sql”,包括了规则在内的所有用户对象,除了索引之外,所有用户对象皆存在于dbo.sysobjects中。
2007-11-29 15:13 |  

            

载走了


2007-12-11 15:29 |  

            

mark
2007-12-27 16:07 |  

 218.66.159.*         

好東西,謝謝樓主分享
2008-01-15 08:31 |

 219.238.217.*         

好 ,收藏!期待楼主其他文章
2008-03-05 12:57 |

 222.180.192.*         

不错哦
2008-04-03 08:28 |

 61.178.78.*         

谢谢楼主,我工作时间用到你的这篇文章了,。
2008-06-17 23:59 |

            

好东西,收藏了
2008-08-16 13:26 |  

            

非常感谢!
不过,应该过滤系统表,只显示用户表就足够了,一般不会用到系统表的吧
2008-09-04 15:56 |  

 221.220.113.*         

强大!学习!
 
转自:

转载于:https://www.cnblogs.com/qiantuwuliang/archive/2009/06/06/1497372.html

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
DirectX修复工具(DirectX Repair)是一款系统级工具软件,简便易用。本程序为绿色版,无需安装,可直接运行。 本程序的主要功能是检测当前系统的DirectX状态,如果发现异常则进行修复。程序主要针对0xc000007b问题设计,可以完美修复该问题。本程序中包含了最新版的DirectX redist(Jun2010),并且全部DX文件都有Microsoft的数字签名,安全放心。 本程序为了应对一般电脑用户的使用,采用了易用的一键式设计,只要点击主界面上的“检测并修复”按钮,程序就会自动完成校验、检测、下载、修复以及注册的全部功能,无需用户的介入,大大降低了使用难度。在常规修复过程中,程序还会自动检测DirectX加速状态,在异常时给予用户相应提示。 本程序适用于多个操作系统,如Windows XP(需先安装.NET 2.0,详情请参阅“致Windows XP用户.txt”文件)、Windows Vista、Windows 7、Windows 8、Windows 8.1、Windows 8.1 Update、Windows 10,同时兼容32位操作系统和64位操作系统。本程序会根据系统的不同,自动调整任务模式,无需用户进行设置。 本程序的V4.0版分为标准版、增强版以及在线修复版。所有版本都支持修复DirectX的功能,而增强版则额外支持修复c++的功能。在线修复版功能与标准版相同,但其所需的数据包需要在修复时自动下载。各个版本之间,主程序完全相同,只是其配套使用的数据包不同。因此,标准版和在线修复版可以通过补全扩展包的形式成为增强版。本程序自V3.5版起,自带扩展功能。只要在主界面的“工具”菜单下打开“选项”对话框,找到“扩展”标签,点击其中的“开始扩展”按钮即可。扩展过程需要Internet连接,扩展成功后新的数据包可自动生效。扩展用时根据网络速度不同而不同,最快仅需数秒,最慢需要数分钟,烦请耐心等待。如扩展失败,可点击“扩展”界面左上角小锁图标切换为加密连接,即可很大程度上避免因防火墙或其他原因导致的连接失败。 本程序自V2.0版起采用全新的底层程序架构,使用了异步多线程编程技术,使得检测、下载、修复单独进行,互不干扰,快速如飞。新程序更改了自我校验方式,因此使用新版本的程序时不会再出现自我校验失败的错误;但并非取消自我校验,因此程序安全性与之前版本相同,并未降低。 程序有更新系统c++功能。由于绝大多数软件运行时需要c++的支持,并且c++的异常也会导致0xc000007b错误,因此程序在检测修复的同时,也会根据需要更新系统中的c++组件。自V3.2版本开始使用了全新的c++扩展包,可以大幅提高工业软件修复成功的概率。修复c++的功能仅限于增强版,标准版及在线修复版在系统c++异常时(非丢失时)会提示用户使用增强版进行修复。除常规修复外,新版程序还支持C++强力修复功能。当常规修复无效时,可以到本程序的选项界面内开启强力修复功能,可大幅提高修复成功率。请注意,请仅在常规修复无效时再使用此功能。 程序有两种窗口样式。正常模式即默认样式,适合绝大多数用户使用。另有一种简约模式,此时窗口将只显示最基本的内容,修复会自动进行,修复完成10秒钟后会自动退出。该窗口样式可以使修复工作变得更加简单快速,同时方便其他软件、游戏将本程序内嵌,即可进行无需人工参与的快速修复。开启简约模式的方法是:打开程序所在目录下的“Settings.ini”文件(如果没有可以自己创建),将其中的“FormStyle”一项的值改为“Simple”并保存即可。 新版程序支持命令行运行模式。在命令行中调用本程序,可以在路径后直接添加命令进行相应的设置。常见的命令有7类,分别是设置语言的命令、设置窗口模式的命令,设置安全级别的命令、开启强力修复的命令、设置c++修复模式的命令、控制Direct加速的命令、显示版权信息的命令。具体命令名称可以通过“/help”或“/?”进行查询。 程序有高级筛选功能,开启该功能后用户可以自主选择要修复的文件,避免了其他不必要的修复工作。同时,也支持通过文件进行辅助筛选,只要在程序目录下建立“Filter.dat”文件,其中的每一行写一个需要修复文件的序号即可。该功能仅针对高级用户使用,并且必须在正常窗口模式下才有效(简约模式时无效)。 本程序有自动记录日志功能,可以记录每一次检测修复结果,方便在出现问题时,及时分析和查找原因,以便找到解决办法。 程序的“选项”对话框中包含了7项高级功能。点击"常规”选项卡可以调整程序的基本运行情况,包括日志记录、安全级别控制、调试模式开启等。只有开启调试模式后才能在C
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页