导读:1建站知识下面的语句将实现获取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.网站建设哪家好cdeseo网站排名优化软件fault = 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'为你想要查找的数据表。

关键词标签: 实例 SQL 表字