以前有个写一个很不错的,
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
order
by
a.id,a.colorder
我也按字的方法来读,
但是今天发现,主键,信息不是很正确,
一看代码,读主键,那要跟索引结合起来的,
于是上网找相关资料,找了半天,也没有找到,
于是就自己看SQL server的帮助,
最后发现,
syscolumns的status为128就是主键,
SELECT
ColumnName
=
a.name, Type
=
b.name,Length3
=
COLUMNPROPERTY
(a.id,a.name,
'
PRECISION
'
), IsKey
=
CASE
a.status
WHEN
128
THEN
'
1
'
ELSE
'
0
'
END
, Length1
=
a.length, Length2
=
isnull
(
COLUMNPROPERTY
(a.id, a.name,
'
Scale
'
),
0
),IsIdentity
=
CASE
WHEN
COLUMNPROPERTY
(a.id, a.name,
'
IsIdentity
'
)
=
1
THEN
'
1
'
ELSE
'
0
'
END
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
d .name
=
'
Users
'
ORDER
BY
a.id, a.colorder