如何利用三个SQL视图查出所有SQLServer数据库字典

本篇内容主要讲解“如何利用三个SQL视图查出所有SQLServer数据库字典”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“如何利用三个SQL视图查出所有SQLServer数据库字典”吧!

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

SELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,a.colorderAS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS标识,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主键,b.nameAS类型,a.lengthAS长度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小数位数,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允许空,ISNULL(e.text,'')AS默认值,ISNULL(g.[value],'')AS字段说明,d.crdateAS创建时间,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改时间FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOINdbo.syspropertiesgONa.id=g.idANDa.colid=g.smallidANDg.name='MS_Description'LEFTOUTERJOINdbo.syspropertiesfONd.id=f.idANDf.smallid=0ANDf.name='MS_Description'ORDERBYd.name,a.colorderSqlServer2005数据库字典--表结构.sqlSELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,a.colorderAS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS标识,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主键,b.nameAS类型,a.lengthAS长度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小数位数,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允许空,ISNULL(e.text,'')AS默认值,ISNULL(g.[value],'')AS字段说明,d.crdateAS创建时间,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改时间FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOINsys.extended_propertiesgONa.id=g.major_idANDa.colid=g.minor_idANDg.name='MS_Description'LEFTOUTERJOINsys.extended_propertiesfONd.id=f.major_idANDf.minor_id=0ANDf.name='MS_Description'ORDERBYd.name,字段序号

　　怎样利用三个SQL视图查出所有SQLServer数据库字典

2.SQLServer数据库字典--索引.sql
　　SELECTTOP100PERCENT--a.id,CASEWHENb.keyno=1THENc.nameELSE''ENDAS表名,CASEWHENb.keyno=1THENa.nameELSE''ENDAS索引名称,d.nameAS列名,b.keynoAS索引顺序,CASEindexkey_property(c.id,b.indid,b.keyno,'isdescending')WHEN1THEN'降序'WHEN0THEN'升序'ENDAS排序,CASEWHENp.idISNULLTHEN''ELSE'√'ENDAS主键,CASEINDEXPROPERTY(c.id,a.name,'IsClustered')WHEN1THEN'√'WHEN0THEN''ENDAS聚集,CASEINDEXPROPERTY(c.id,a.name,'IsUnique')WHEN1THEN'√'WHEN0THEN''ENDAS唯一,CASEWHENe.idISNULLTHEN''ELSE'√'ENDAS唯一约束,a.OrigFillFactorAS填充因子,c.crdateAS创建时间,c.refdateAS更改时间FROMdbo.sysindexesaINNERJOINdbo.sysindexkeysbONa.id=b.idANDa.indid=b.indidINNERJOINdbo.syscolumnsdONb.id=d.idANDb.colid=d.colidINNERJOINdbo.sysobjectscONa.id=c.idANDc.xtype='U'LEFTOUTERJOINdbo.sysobjectseONe.name=a.nameANDe.xtype='UQ'LEFTOUTERJOINdbo.sysobjectspONp.name=a.nameANDp.xtype='PK'WHERE(OBJECTPROPERTY(a.id,N'IsUserTable')=1)AND(OBJECTPROPERTY(a.id,N'IsMSShipped')=0)AND(INDEXPROPERTY(a.id,a.name,'IsAutoStatistics')=0)ORDERBYc.name,a.name,b.keyno

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

SELECTDISTINCTTOP100PERCENTisnull(p.name,'')AS父对象,o.xtype,CASEo.xtypeWHEN'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'规则'ELSENULLENDAS类型,o.nameAS对象名,o.crdateAS创建时间,o.refdateAS更改时间,c.textAS声明语句,OBJECTPROPERTY(o.id,N'IsMSShipped')FROMdbo.sysobjectsoLeftJOINdbo.sysobjectspONo.parent_obj=p.idLEFTOUTERJOINdbo.syscommentscONo.id=c.idWHERE--(o.xtypeIN('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')。

到此，相信大家对“如何利用三个SQL视图查出所有SQLServer数据库字典”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！