SELECT
obj.
object_id
, obj.name table_name, c.column_id, c.name column_name, c.system_type_id, c.is_nullable,
c.is_identity, c.is_computed, ind.is_primary_key, c.max_length,
COLUMNPROPERTY
( c.
object_id
, c.
[
name
]
,
'
Precision
'
)
Precision
,
ISNULL
(
COLUMNPROPERTY
( c.
object_id
, c.
[
name
]
,
'
Scale
'
),
0
) Scale, d.definition
[
Default
]
FROM
sys.columns c
INNER
JOIN
sys.objects obj
on
c.
object_id
=
obj.
object_id
LEFT
JOIN
sys.default_constraints d
on
c.default_object_id
=
d.
object_id
LEFT
JOIN
sys.index_columns ic
ON
c.
object_id
=
ic.
object_id
AND
c.column_id
=
ic.column_id
LEFT
JOIN
sys.indexes ind
ON
c.
object_id
=
ind.
object_id
AND
ic.index_id
=
ind.index_id
WHERE
obj.type
LIKE
'
U
'
--
AND obj.[name] LIKE 'Log' -- 需要查看某个表信息时添加此条件
注:仅适用于2005版本,因为2000的代码已有邹老师的标准版本,CSDN上一大把,故不累述。
以上代码参考邹老师为2000写的代码,查阅2005帮助后,列出一些较为实用的属性,其实sys.columns里还有很多有用的属性,但不是经常使用,故不列出,如有兴趣可以自行添加列名列出。