刚好需要这个过程, 在网上找了几个, 感觉都差一些, 其SQL都特别长. 而且我特别需要用到聚类索引, 自己写了个, 这是程序开发使用到的版本.
select
o.name tablename, c.name fieldname, t.name fieldtype,
columnproperty
(c.id,c.name,
'
PRECISION
'
) fieldlen, c.Scale,c.length, c.colid fieldorder, c.isnullable,
case
when
c.colid
in
(
select
ik.colid
from
sysindexes i, Sysindexkeys ik, sysobjects oo
where
i.id
=
ik.id
and
i.indid
=
ik.indid
and
i.name
=
oo.name
and
oo.xtype
=
'
PK
'
--
主键
and
o.id
=
i.id )
then
1
else
0
end
isPrimaryKey,
case
when
c.colid
in
(
select
ik.colid
from
sysindexes i, Sysindexkeys ik
where
i.id
=
ik.id
and
i.indid
=
ik.indid
and
o.id
=
i.id
and
i.indid
=
1
--
聚类索引
)
then
1
else
0
end
isClusterKey,
columnproperty
( c.id, c.name,
'
IsIdentity
'
) IsIdentity,
isnull
(m.
text
,
''
) defaultvalue
from
sysobjects o, syscolumns c, systypes t, syscomments m
where
o.xtype
=
'
U
'
and
o.id
=
c.id
and
c.xtype
=
t.xtype
and
c.cdefault
*=
m.id
order
by
o.name, c.colid
程序使用到的版本是否用0/1表示, 如果用在文档整理, 难看得要命, 优化了个文档版本的,在查询分析器找出来还是蛮好看的.
select
case
when
c.colid
=
1
then
o.name
else
''
end
表名, c.colid 顺序, c.name 字段名, t.name 字段类型,
columnproperty
(c.id,c.name,
'
PRECISION
'
) 字段长度,
isnull
(c.Scale,
''
) 小数位, c.length 占用字节,
case
when
c.isnullable
=
1
then
'
√
'
else
''
end
可为空,
case
when
c.colid
in
(
select
ik.colid
from
sysindexes i, Sysindexkeys ik, sysobjects oo
where
i.id
=
ik.id
and
i.indid
=
ik.indid
and
i.name
=
oo.name
and
oo.xtype
=
'
PK
'
--
主键
and
o.id
=
i.id )
then
'
√
'
else
''
end
主键,
case
when
c.colid
in
(
select
ik.colid
from
sysindexes i, Sysindexkeys ik
where
i.id
=
ik.id
and
i.indid
=
ik.indid
and
o.id
=
i.id
and
i.indid
=
1
--
聚类索引
)
then
'
√
'
else
''
end
聚类索引,
case
when
columnproperty
( c.id, c.name,
'
IsIdentity
'
)
=
1
then
'
√
'
else
''
end
自增长,
isnull
(m.
text
,
''
) 默认值
from
sysobjects o, syscolumns c, systypes t, syscomments m
where
o.xtype
=
'
U
'
and
o.id
=
c.id
and
c.xtype
=
t.xtype
and
c.cdefault
*=
m.id
order
by
o.name, c.colid
扫一扫
6110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
