SQL SERVER 2000 和 2005结构是不一样的，所以在获取SQL SERVER 2000/2005字段属性时需要分别来获取。
1。SQL SERVER 2000
1
2SELECT
3 字段名 = a.name,
4 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
5 SELECT name FROM sysindexes WHERE indid in(
6 SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,
7 标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name,
8 'IsIdentity') = 1 THEN '1' ELSE '0' END,
9 类型 = b.name,
10 占用字节数 = a.length,
11 长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
12 小数 = a.xscale,
13 可空 = a.isnullable,
14 默认值 = isnull(e.text,''),
15 字段说明 = isnull(g.[value],'')
16FROM
17 syscolumns a
18left join
19 systypes b
20on
21 a.xusertype=b.xusertype
22inner join
23 sysobjects d
24on
25 a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
26left join
27 syscomments e
28on
29 a.cdefault=e.id
30left join
31 sysproperties g
32on
33 a.id=g.id and a.colid=g.smallid
34left join
35 sysproperties f
36on
37 d.id=f.id and f.smallid=0
38where
39 d.name='UserAccount'
2
SELECT 3
字段名 = a.name,4
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (5
SELECT name FROM sysindexes WHERE indid in(6
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,7
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 8
'IsIdentity') = 1 THEN '1' ELSE '0' END, 9
类型 = b.name,10
占用字节数 = a.length,11
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),12
小数 = a.xscale,13
可空 = a.isnullable,14
默认值 = isnull(e.text,''),15
字段说明 = isnull(g.[value],'')16
FROM 17
syscolumns a18
left join 19
systypes b 20
on 21
a.xusertype=b.xusertype22
inner join 23
sysobjects d 24
on 25
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'26
left join 27
syscomments e 28
on 29
a.cdefault=e.id30
left join 31
sysproperties g 32
on 33
a.id=g.id and a.colid=g.smallid 34
left join 35
sysproperties f 36
on 37
d.id=f.id and f.smallid=038
where 39
d.name='UserAccount'd.name = 'UserAccount'，UserAccount为你需要查找的数据表。
2。SQL SERVER 2005
1SELECT CASE WHEN EXISTS
2 (SELECT 1
3 FROM sysobjects
4 WHERE xtype = 'PK' AND parent_obj = a.id AND name IN
5 (SELECT name
6 FROM sysindexes
7 WHERE indid IN
8 (SELECT indid
9 FROM sysindexkeys
10 WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,
11 '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,
12 'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'
13FROM sys.syscolumns AS a INNER JOIN
14 sys.sysobjects AS b ON a.id = b.id INNER JOIN
15 sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
16 sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
17 sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
18WHERE (b.name = 'keyfactory') AND (c.status <> '1')
SELECT CASE WHEN EXISTS2
(SELECT 13
FROM sysobjects4
WHERE xtype = 'PK' AND parent_obj = a.id AND name IN5
(SELECT name6
FROM sysindexes7
WHERE indid IN8
(SELECT indid9
FROM sysindexkeys10
WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name, 11
'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, 12
'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'13
FROM sys.syscolumns AS a INNER JOIN14
sys.sysobjects AS b ON a.id = b.id INNER JOIN15
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN16
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN17
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id18
WHERE (b.name = 'keyfactory') AND (c.status <> '1')b.name = 'Keyfactory','Keyfactory'为你想要查找的数据表。