SQL Server 查詢資料表欄位定義的標準系統表寫法

推薦寫法：使用 INFORMATION_SCHEMA (ANSI 標準、跨版本穩定)

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT,
    ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'  -- 表名
  AND TABLE_SCHEMA = 'dbo';         -- 結構描述 (預設 dbo)

優點： 符合 ANSI SQL 標準，SQL Server 2000 至 2022 所有版本支援，語意清晰，不依賴內部系統表結構變動。

進階版：包含主鍵、身分識、擴展屬性 (說明)

SELECT 
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.IS_NULLABLE,
    c.COLUMN_DEFAULT,
    c.ORDINAL_POSITION,
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PK' ELSE '' END AS KEY_TYPE,
    CASE WHEN ic.OBJECT_ID IS NOT NULL THEN 'IDENTITY' ELSE '' END AS IDENTITY_FLAG,
    ep.VALUE AS COLUMN_DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
    SELECT ku.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku 
      ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = 'YourTableName' 
      AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN sys.identity_columns ic 
  ON ic.OBJECT_ID = OBJECT_ID('YourTableName') 
 AND ic.NAME = c.COLUMN_NAME
LEFT JOIN sys.extended_properties ep
  ON ep.MAJOR_ID = OBJECT_ID('YourTableName')
 AND ep.MINOR_ID = c.ORDINAL_POSITION
 AND ep.NAME = 'MS_Description'
WHERE c.TABLE_NAME = 'YourTableName'
  AND c.TABLE_SCHEMA = 'dbo'
ORDER BY c.ORDINAL_POSITION;

舊版相容寫法：syscolumns + sysobjects (SQL Server 2000 風格，不建議新專案使用)

SELECT a.name AS COLUMN_NAME
FROM syscolumns a
JOIN sysobjects b ON a.id = b.id
WHERE b.id = OBJECT_ID('YourTableName')
  AND b.xtype = 'U';  -- User Table

缺點： 僅回傳欄位名稱，缺乏型別、長度、空值、預設值等元資料；`syscolumns`/`sysobjects` 為相容性視圖，未來版本可能移除。

最現代寫法：sys.columns + sys.tables + sys.types (內部目錄視圖、效能最佳、功能最全)

SELECT 
    c.name AS COLUMN_NAME,
    t.name AS DATA_TYPE,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity,
    c.default_object_id,
    OBJECT_DEFINITION(c.default_object_id) AS DEFAULT_DEFINITION,
    ep.value AS DESCRIPTION
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.extended_properties ep
  ON ep.major_id = c.object_id
 AND ep.minor_id = c.column_id
 AND ep.name = 'MS_Description'
WHERE t.name = 'YourTableName'
  AND SCHEMA_NAME(t.schema_id) = 'dbo'
ORDER BY c.column_id;

快速取得單一資料表所有欄位名 (逗號分隔，動態 SQL 常用)

DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')  -- SQL Server 2017+
-- 舊版：SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA = 'dbo';
PRINT @cols;