获取SQL Server表字段的各种属性实例代码

-- SQL Server 2000
SELECT a.name AS 字段名, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = PK AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid)))
THEN 1 ELSE 0 END AS 主键, CASE WHEN COLUMNPROPERTY(a.id, a.name,
IsIdentity) = 1 THEN 1 ELSE 0 END AS 标识, b.name AS 类型,
a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, PRECISION) AS 长度,
a.xscale AS 小数, a.isnullable AS 可空, ISNULL(e.text, ) AS 默认值, ISNULL(g.[value],
) AS 字段说明
FROM syscolumns a LEFT OUTER JOIN
systypes b ON a.xusertype = b.xusertype INNER JOIN
sysobjects d ON a.id = d.id AND d.xtype = U AND
d.name <> dtproperties LEFT OUTER JOIN
syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN
sysproperties f ON d.id = f.id AND f.smallid = 0
WHERE (d.name = 表名称)
--2 。SQL SERVER 2005
SELECT CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = PK AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN 1 ELSE 0 END AS key, CASE WHEN COLUMNPROPERTY(a.id, a.name,
IsIdentity) = 1 THEN 1 ELSE 0 END AS identity, a.name AS ColName, c.name AS TypeName, a.length AS byte, COLUMNPROPERTY(a.id, a.name,
PRECISION) AS length, a.xscale, a.isnullable, ISNULL(e.text, ) AS default, ISNULL(p.value, ) AS comment
FROM sys.syscolumns AS a INNER JOIN
sys.sysobjects AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
WHERE (b.name = keyfactory) AND (c.status <> 1)
--b.name = Keyfactory,Keyfactory为你想要查找的数据表 。

--2、SQL SERVER 2005
SELECT CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = PK AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN 1 ELSE 0 END AS key, CASE WHEN COLUMNPROPERTY(a.id, a.name,
IsIdentity) = 1 THEN 1 ELSE 0 END AS identity, a.name AS ColName, c.name AS TypeName, a.length AS byte, COLUMNPROPERTY(a.id, a.name,
PRECISION) AS length, a.xscale, a.isnullable, ISNULL(e.text, ) AS default, ISNULL(p.value, ) AS comment
FROM sys.syscolumns AS a INNER JOIN
sys.sysobjects AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
WHERE (b.name = keyfactory) AND (c.status <> 1)
--b.name = Keyfactory,Keyfactory为你想要查找的数据表 。