SELECT
表名
=
case
when
a.colorder
=
1
then
d.name
else
''
end
, 字段序号
=
a.colorder, 字段名
=
a.name, 标识
=
case
when
COLUMNPROPERTY
( a.id,a.name,
'
IsIdentity
'
)
=
1
then
'
√
'
else
''
end
, 主键
=
case
when
exists
(
SELECT
1
FROM
sysobjects
where
xtype
=
'
PK
'
and
name
in
(
SELECT
name
FROM
sysindexes
WHERE
indid
in
(
SELECT
indid
FROM
sysindexkeys
WHERE
id
=
a.id
AND
colid
=
a.colid )))
then
'
√
'
else
''
end
, 类型
=
b.name, 占用字节数
=
a.length, 长度
=
COLUMNPROPERTY
(a.id,a.name,
'
PRECISION
'
), 小数位数
=
isnull
(
COLUMNPROPERTY
(a.id,a.name,
'
Scale
'
),
0
), 允许空
=
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
, 默认值
=
isnull
(e.
text
,
''
), 字段说明
=
isnull
(g.
[
value
]
,
''
)
FROM
syscolumns a
left
join
systypes b
on
a.xtype
=
b.xusertype
inner
join
sysobjects d
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
d.name
<>
'
dtproperties
'
left
join
syscomments e
on
a.cdefault
=
e.id
left
join
sysproperties g
on
a.id
=
g.id
and
a.colid
=
g.smallid
where
g.name
=
'
MS_Description
'
order
by
a.id,a.colorder
详细版本：
SELECT
表名
=
case
when
a.colorder
=
1
then
d.name
else
''
end
, 表说明
=
case
when
a.colorder
=
1
then
isnull
(f.value,
''
)
else
''
end
, 字段序号
=
a.colorder, 字段名
=
a.name, 标识
=
case
when
COLUMNPROPERTY
( a.id,a.name,
'
IsIdentity
'
)
=
1
then
'
√
'
else
''
end
, 主键
=
case
when
exists
(
SELECT
1
FROM
sysobjects
where
xtype
=
'
PK
'
and
name
in
(
SELECT
name
FROM
sysindexes
WHERE
indid
in
(
SELECT
indid
FROM
sysindexkeys
WHERE
id
=
a.id
AND
colid
=
a.colid )))
then
'
√
'
else
''
end
, 类型
=
b.name, 占用字节数
=
a.length, 长度
=
COLUMNPROPERTY
(a.id,a.name,
'
PRECISION
'
), 小数位数
=
isnull
(
COLUMNPROPERTY
(a.id,a.name,
'
Scale
'
),
0
), 允许空
=
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
, 默认值
=
isnull
(e.
text
,
''
), 字段说明
=
isnull
(g.
[
value
]
,
''
), 索引名称
=
isnull
(h.索引名称,
''
), 索引顺序
=
isnull
(h.排序,
''
)
FROM
syscolumns a
left
join
systypes b
on
a.xtype
=
b.xusertype
inner
join
sysobjects d
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
d.name
<>
'
dtproperties
'
left
join
syscomments e
on
a.cdefault
=
e.id
left
join
sysproperties g
on
a.id
=
g.id
and
a.colid
=
g.smallid
left
join
sysproperties f
on
d.id
=
f.id
and
f.smallid
=
0
left
join
(
--
这部分是索引信息,如果要显示索引与表及字段的对应关系,可以只要此部分
select
索引名称
=
a.name,c.id,d.colid ,排序
=
case
indexkey_property(c.id,b.indid,b.keyno,
'
isdescending
'
)
when
1
then
'
降序
'
when
0
then
'
升序
'
end
from
sysindexes a
join
sysindexkeys b
on
a.id
=
b.id
and
a.indid
=
b.indid
join
(
--
这里的作用是有多个索引时,取索引号最小的那个
select
id,colid,indid
=
min
(indid)
from
sysindexkeys
group
by
id,colid) b1
on
b.id
=
b1.id
and
b.colid
=
b1.colid
and
b.indid
=
b1.indid
join
sysobjects c
on
b.id
=
c.id
and
c.xtype
=
'
U
'
and
c.name
<>
'
dtproperties
'
join
syscolumns d
on
b.id
=
d.id
and
b.colid
=
d.colid
where
a.indid
not
in
(
0
,
255
) ) h
on
a.id
=
h.id
and
a.colid
=
h.colid
where
g.name
=
'
MS_Description
'
--
where d.name='要查询的表' --如果只查询指定表,加上此条件
order
by
a.id,a.colorder
以上两段SQL语句可查证来源于邹建 http://blog.csdn.net/zjcxc 并经过本人少量修改。
242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
