字典的"/>
建立sql数据字典的
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
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
更多推荐
建立sql数据字典的
发布评论