use
Roy
--
數據庫
go
--
2005實現數據庫表字段屬性統計(2000里的系統表sysproperties描述表不存在，2005里用sys.extended_properties視圖替代)
select
[
表名
]
=
c.Name,
[
表说明
]
=
isnull
(f.
[
value
]
,
''
),
[
列名
]
=
a.Name,
[
列序號
]
=
a.Column_id,
[
標識
]
=
case
when
is_identity
=
1
then
'
√
'
else
''
end
,
[
主鍵
]
=
case
when
exists
(
select
1
from
sys.objects x
join
sys.indexes y
on
x.Type
=
N
'
PK
'
and
x.Name
=
y.Name
join
sysindexkeys z
on
z.ID
=
a.
Object_id
and x.parent_object_id = z.id
and
z.indid
=
y.index_id
and
z.Colid
=
a.Column_id)
then
'
√
'
else
''
end
,
[
類型
]
=
b.Name,
[
字節數
]
=
case
when
a.
[
max_length
]
=-
1
and
b.Name
!=
'
xml
'
then
'
max/2G
'
when
b.Name
=
'
xml
'
then
'
2^31-1字節/2G
'
else
rtrim
(a.
[
max_length
]
)
end
,
[
長度
]
=
case
when
ColumnProperty
(a.
object_id
,a.Name,
'
Precision
'
)
=-
1
then
'
2^31-1
'
else
rtrim
(
ColumnProperty
(a.
object_id
,a.Name,
'
Precision
'
))
end
,
[
小數
]
=
isnull
(
ColumnProperty
(a.
object_id
,a.Name,
'
Scale
'
),
0
),
[
是否為空
]
=
case
when
a.is_nullable
=
1
then
'
√
'
else
''
end
,
[
列说明
]
=
isnull
(e.
[
value
]
,
''
),
[
默認值
]
=
isnull
(d.
text
,
''
)
from
sys.columns a
left
join
sys.types b
on
a.user_type_id
=
b.user_type_id
inner
join
sys.objects c
on
a.
object_id
=
c.
object_id
and
c.Type
=
'
U
'
left
join
syscomments d
on
a.default_object_id
=
d.ID
left
join
sys.extended_properties e
on
e.major_id
=
c.
object_id
and
e.minor_id
=
a.Column_id
and
e.class
=
1
left
join
sys.extended_properties f
on
f.major_id
=
c.
object_id
and
f.minor_id
=
0
and
f.class
=
1
3748
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
