複製代碼 代碼如下:-- 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'為你想要尋找的資料表。
