网上摘录的,比较详细的3个视图sql,功能很好很强大,以后根据需要可以自己修改,快速得到想要的结果.
代码如下:
表结构:
1
--
SqlServer2005数据库字典--表结构.sql
2
SELECT
TOP
100
PERCENT
--
a.id,
3
CASE
WHEN
a.colorder
=
1
THEN
d.name
ELSE
''
END
AS
表名,
4
CASE
WHEN
a.colorder
=
1
THEN
isnull
(f.value,
''
)
ELSE
''
END
AS
表说明,
5
a.colorder
AS
字段序号, a.name
AS
字段名,
CASE
WHEN
COLUMNPROPERTY
(a.id,
6
a.name,
'
IsIdentity
'
)
=
1
THEN
'
√
'
ELSE
''
END
AS
标识,
7
CASE
WHEN
EXISTS
8
(
SELECT
1
9
FROM
dbo.sysindexes si
INNER
JOIN
10
dbo.sysindexkeys sik
ON
si.id
=
sik.id
AND
si.indid
=
sik.indid
INNER
JOIN
11
dbo.syscolumns sc
ON
sc.id
=
sik.id
AND
sc.colid
=
sik.colid
INNER
JOIN
12
dbo.sysobjects so
ON
so.name
=
si.name
AND
so.xtype
=
'
PK
'
13
WHERE
sc.id
=
a.id
AND
sc.colid
=
a.colid)
THEN
'
√
'
ELSE
''
END
AS
主键,
14
b.name
AS
类型, a.length
AS
长度,
COLUMNPROPERTY
(a.id, a.name,
'
PRECISION
'
)
15
AS
精度,
ISNULL
(
COLUMNPROPERTY
(a.id, a.name,
'
Scale
'
),
0
)
AS
小数位数,
16
CASE
WHEN
a.isnullable
=
1
THEN
'
√
'
ELSE
''
END
AS
允许空,
ISNULL
(e.
text
,
''
)
17
AS
默认值,
ISNULL
(g.
[
value
]
,
''
)
AS
字段说明, d.crdate
AS
创建时间,
18
CASE
WHEN
a.colorder
=
1
THEN
d.refdate
ELSE
NULL
END
AS
更改时间
19
FROM
dbo.syscolumns a
LEFT
OUTER
JOIN
20
dbo.systypes b
ON
a.xtype
=
b.xusertype
INNER
JOIN
21
dbo.sysobjects d
ON
a.id
=
d.id
AND
d.xtype
=
'
U
'
AND
22
d.status
>=
0
LEFT
OUTER
JOIN
23
dbo.syscomments e
ON
a.cdefault
=
e.id
LEFT
OUTER
JOIN
24
sys.extended_properties g
ON
a.id
=
g.major_id
AND
a.colid
=
g.minor_id
AND
25
g.name
=
'
MS_Description
'
LEFT
OUTER
JOIN
26
sys.extended_properties f
ON
d.id
=
f.major_id
AND
f.minor_id
=
0
AND
27
f.name
=
'
MS_Description
'
28
ORDER
BY
d.name, 字段序号
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
索引:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
表.视图.函数.存储过程.触发器.主键.外键.约束.规则:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
oracle的数据字典哪天有空自己整理下.