注意, sysproperties 因为是无正式文档记载的系统表,已经在 Sql2005 中废弃了, Sql2005 中使用 Sys.extended_properites 保存用户自定义的表、字段的属性信息。
原先的脚本:
1
SELECT
2
(
case
when
a.colorder
=
1
then
d.name
else
''
end
)表名,
3
a.colorder 字段序号,
4
a.name 字段名,
5
(
case
when
COLUMNPROPERTY
( a.id,a.name,
'
IsIdentity
'
)
=
1
then
'
√
'
else
''
end
) 标识,
6
(
case
when
(
SELECT
count
(
*
)
7
FROM
sysobjects
8
WHERE
(name
in
9
(
SELECT
name
10
FROM
sysindexes
11
WHERE
(id
=
a.id)
AND
(indid
in
12
(
SELECT
indid
13
FROM
sysindexkeys
14
WHERE
(id
=
a.id)
AND
(colid
in
15
(
SELECT
olid
16
FROM
yscolumns
17
WHERE
(id
=
a.id)
AND
(name
=
a.name)))))))
AND
18
(xtype
=
'
PK
'
))
>
0
then
'
√
'
else
''
end
) 主键,
19
b.name 类型,
20
a.length 占用字节数,
21
COLUMNPROPERTY
(a.id,a.name,
'
PRECISION
'
)
as
长度,
22
isnull
(
COLUMNPROPERTY
(a.id,a.name,
'
Scale
'
),
0
)
as
小数位数,
23
(
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
) 允许空,
24
isnull
(e.
text
,
''
) 默认值,
25
isnull
(g.
[
value
]
,
''
)
AS
字段说明
26![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
FROM
syscolumns a
left
join
systypes b
28
on
a.xtype
=
b.xusertype
29
inner
join
sysobjects d
30
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
.name
<>
'
dtproperties
'
31
left
join
syscomments e
32
on
a.cdefault
=
e.id
33
left
join
sysproperties g
34
on
a.id
=
g.id
AND
a.colid
=
g.smallid
35
order
by
a.id,a.colorder
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
改为
1
SELECT
2
(case when a.colorder=1 then d.name else '' end)表名,
3
a.colorder 字段序号,
4
a.name 字段名,
5
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
6
(case when (SELECT count(*)
7
FROM sysobjects
8
WHERE (name in
9
(SELECT name
10
FROM sysindexes
11
WHERE (id = a.id) AND (indid in
12
(SELECT indid
13
FROM sysindexkeys
14
WHERE (id = a.id) AND (colid in
15
(SELECT colid
16
FROM syscolumns
17
WHERE (id = a.id) AND (name = a.name))))))) AND
18
(xtype = 'PK'))>0 then '√' else '' end) 主键,
19
b.name 类型,
20
a.length 占用字节数,
21
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
22
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
23
(case when a.isnullable=1 then '√'else '' end) 允许空,
24
isnull(e.text,'') 默认值,
25
isnull(g.[value],'') AS 字段说明
26
27
FROM syscolumns a left join systypes b
28
on a.xtype=b.xusertype
29
inner join sysobjects d
30
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
31
left join syscomments e
32
on a.cdefault=e.id
33
left join sys.extended_properties g
34
on a.id=g.major_id AND a.colid = g.major_id
35
order by a.id,a.colorder
36
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)