复制代码 代码如下: -- 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 sysinde
复制代码 代码如下: -- 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'为你想要查找的数据表。
