网上有很多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.sysindexessi
INNER
JOIN
dbo.sysindexkeyssik
ON
si.id
=
sik.id
AND
si.indid
=
sik.indid
INNER
JOIN
dbo.syscolumnssc
ON
sc.id
=
sik.id
AND
sc.colid
=
sik.colid
INNER
JOIN
dbo.sysobjectsso
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.syscolumnsa
LEFT
OUTER
JOIN
dbo.systypesb
ON
a.xtype
=
b.xusertype
INNER
JOIN
dbo.sysobjectsd
ON
a.id
=
d.id
AND
d.xtype
=
'
U
'
AND
d.status
>=
0
LEFT
OUTER
JOIN
dbo.syscommentse
ON
a.cdefault
=
e.id
LEFT
OUTER
JOIN
dbo.syspropertiesg
ON
a.id
=
g.id
AND
a.colid
=
g.smallid
AND
g.name
=
'
MS_Description
'
LEFT
OUTER
JOIN
dbo.syspropertiesf
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.sysindexessi
INNER
JOIN
dbo.sysindexkeyssik
ON
si.id
=
sik.id
AND
si.indid
=
sik.indid
INNER
JOIN
dbo.syscolumnssc
ON
sc.id
=
sik.id
AND
sc.colid
=
sik.colid
INNER
JOIN
dbo.sysobjectsso
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.syscolumnsa
LEFT
OUTER
JOIN
dbo.systypesb
ON
a.xtype
=
b.xusertype
INNER
JOIN
dbo.sysobjectsd
ON
a.id
=
d.id
AND
d.xtype
=
'
U
'
AND
d.status
>=
0
LEFT
OUTER
JOIN
dbo.syscommentse
ON
a.cdefault
=
e.id
LEFT
OUTER
JOIN
sys.extended_propertiesg
ON
a.id
=
g.major_id
AND
a.colid
=
g.minor_id
AND
g.name
=
'
MS_Description
'
LEFT
OUTER
JOIN
sys.extended_propertiesf
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.sysindexesa
INNER
JOIN
dbo.sysindexkeysb
ON
a.id
=
b.id
AND
a.indid
=
b.indid
INNER
JOIN
dbo.syscolumnsd
ON
b.id
=
d.id
AND
b.colid
=
d.colid
INNER
JOIN
dbo.sysobjectsc
ON
a.id
=
c.id
AND
c.xtype
=
'
U
'
LEFT
OUTER
JOIN
dbo.sysobjectse
ON
e.name
=
a.name
AND
e.xtype
=
'
UQ
'
LEFT
OUTER
JOIN
dbo.sysobjectsp
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.sysobjectso
Left
JOIN
dbo.sysobjectsp
ON
o.parent_obj
=
p.id
LEFT
OUTER
JOIN
dbo.syscommentsc
ON
o.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
'
)
ORDER
BY
o.xtype
DESC
数据库字典包括表结构(分2K和2005)、 索引和主键.外键.约束.视图.函数.存储过程.触发器.规则。
可以在企业管理器、查询分析器中简单执行,直接了当的查出SQL2K及SQL2005的所有数据字典,方便文档的编写,希望对大家有帮助。
1. SqlServer2000数据库字典--表结构.sql
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
SqlServer2005数据库字典--表结构.sql
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
2. SqlServer数据库字典--索引.sql
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
3. SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则.sql
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)