擷取SQL Server表欄位的各種屬性

-- SQL Server 200

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'為你想要尋找的資料表。