導讀: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 表字