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' -- 需要查看某个表信息时添加此条件
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里还有很多有用的属性,但不是经常使用,故不列出,如有兴趣可以自行添加列名列出。