获取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'为你想要查找的数据表。