`
黄继华
  • 浏览: 42535 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

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

 
阅读更多

数据库字典包括表结构(分2K和2005)、索引和主键.外键.约束.视图.函数.存储过程.触发器.规则。可以在企业管理器、查询分析器中简单执行,直接了当的查出SQL2K及SQL2005的所有数据字典,方便文档的编写,希望对大家有帮助。

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

SELECTTOP100PERCENT--a.id,
CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,
CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,
a.colorder
AS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,
a.name,
'IsIdentity')=1THEN''ELSE''ENDAS标识,
CASEWHENEXISTS
(
SELECT1
FROMdbo.sysindexessiINNERJOIN
dbo.sysindexkeyssik
ONsi.id=sik.idANDsi.indid=sik.indidINNERJOIN
dbo.syscolumnssc
ONsc.id=sik.idANDsc.colid=sik.colidINNERJOIN
dbo.sysobjectsso
ONso.name=si.nameANDso.xtype='PK'
WHEREsc.id=a.idANDsc.colid=a.colid)THEN''ELSE''ENDAS主键,
b.name
AS类型,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.syscolumnsaLEFTOUTERJOIN
dbo.systypesb
ONa.xtype=b.xusertypeINNERJOIN
dbo.sysobjectsd
ONa.id=d.idANDd.xtype='U'AND
d.status
>=0LEFTOUTERJOIN
dbo.syscommentse
ONa.cdefault=e.idLEFTOUTERJOIN
dbo.syspropertiesg
ONa.id=g.idANDa.colid=g.smallidAND
g.name
='MS_Description'LEFTOUTERJOIN
dbo.syspropertiesf
ONd.id=f.idANDf.smallid=0AND
f.name
='MS_Description'
ORDERBYd.name,a.colorder

SqlServer2005数据库字典--表结构.sql
SELECTTOP100PERCENT--a.id,
CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,
CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,
a.colorder
AS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,
a.name,
'IsIdentity')=1THEN''ELSE''ENDAS标识,
CASEWHENEXISTS
(
SELECT1
FROMdbo.sysindexessiINNERJOIN
dbo.sysindexkeyssik
ONsi.id=sik.idANDsi.indid=sik.indidINNERJOIN
dbo.syscolumnssc
ONsc.id=sik.idANDsc.colid=sik.colidINNERJOIN
dbo.sysobjectsso
ONso.name=si.nameANDso.xtype='PK'
WHEREsc.id=a.idANDsc.colid=a.colid)THEN''ELSE''ENDAS主键,
b.name
AS类型,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.syscolumnsaLEFTOUTERJOIN
dbo.systypesb
ONa.xtype=b.xusertypeINNERJOIN
dbo.sysobjectsd
ONa.id=d.idANDd.xtype='U'AND
d.status
>=0LEFTOUTERJOIN
dbo.syscommentse
ONa.cdefault=e.idLEFTOUTERJOIN
sys.extended_propertiesg
ONa.id=g.major_idANDa.colid=g.minor_idAND
g.name
='MS_Description'LEFTOUTERJOIN
sys.extended_propertiesf
ONd.id=f.major_idANDf.minor_id=0AND
f.name
='MS_Description'
ORDERBYd.name,字段序号

2.SqlServer数据库字典--索引.sql
SELECTTOP100PERCENT--a.id,
CASEWHENb.keyno=1THENc.nameELSE''ENDAS表名,
CASEWHENb.keyno=1THENa.nameELSE''ENDAS索引名称,d.nameAS列名,
b.keyno
AS索引顺序,CASEindexkey_property(c.id,b.indid,b.keyno,'isdescending')
WHEN1THEN'降序'WHEN0THEN'升序'ENDAS排序,CASEWHENp.idISNULL
THEN''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.OrigFillFactor
AS填充因子,c.crdateAS创建时间,c.refdateAS更改时间
FROMdbo.sysindexesaINNERJOIN
dbo.sysindexkeysb
ONa.id=b.idANDa.indid=b.indidINNERJOIN
dbo.syscolumnsd
ONb.id=d.idANDb.colid=d.colidINNERJOIN
dbo.sysobjectsc
ONa.id=c.idANDc.xtype='U'LEFTOUTERJOIN
dbo.sysobjectse
ONe.name=a.nameANDe.xtype='UQ'LEFTOUTERJOIN
dbo.sysobjectsp
ONp.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
SELECTDISTINCT
TOP100PERCENTisnull(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'规则'ELSENULL
ENDAS类型,o.nameAS对象名,o.crdateAS创建时间,o.refdateAS更改时间,
c.
textAS声明语句,OBJECTPROPERTY(o.id,N'IsMSShipped')
FROMdbo.sysobjectsoLeftJOIN
dbo.sysobjectsp
ONo.parent_obj=p.idLEFTOUTERJOIN
dbo.syscommentsc
ONo.id=c.id
WHERE--(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')
ORDERBYo.xtypeDESC


分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics