您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 表欄位屬性查詢完整指南：適用於 2000/2005/2008+ 版本

編輯：關於SqlServer

前言

在資料庫開發與維護中，快速獲取資料表欄位的詳細屬性（如主鍵、自增、資料類型、長度、預設值、說明等）是常見需求。本文整理了適用於 SQL Server 2000、2005 及更新版本的實用查詢腳本，幫助您高效掌握表結構資訊。

SQL Server 2000 版本查詢腳本

針對 SQL Server 2000 系統表結構，使用 syscolumns、systypes、sysobjects、syscomments、sysproperties 等系統表關聯查詢：

-- 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 = '您的表名稱';

SQL Server 2005/2008+ 版本查詢腳本

新版本採用 sys.syscolumns、sys.sysobjects、sys.systypes、sys.extended_properties 等系統視圖，語法更規範：

-- SQL Server 2005/2008+
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 = '您的表名稱' AND c.status <> 1;