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.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'          。