標準資訊結構描述檢視 (INFORMATION_SCHEMA) — 跨資料庫可攜
-- 查詢指定資料表所有欄位
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable' AND TABLE_SCHEMA = 'dbo';
系統目錄檢視 (sys.columns) — SQL Server 專用、效能最佳
-- 基礎查詢
SELECT name AS ColumnName, column_id AS ColOrder
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YourTable');
-- 含資料類型、長度、精度、身分識別、預設值
SELECT c.name AS ColumnName,
t.name AS DataType,
c.max_length AS MaxLength,
c.precision, c.scale,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
dc.definition AS DefaultValue
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE c.object_id = OBJECT_ID('dbo.YourTable')
ORDER BY c.column_id;
舊版相容：syscolumns/sysobjects (SQL 2000 風格，不建議新專案使用)
SELECT name FROM syscolumns
WHERE id = OBJECT_ID('YourTable');
實戰：動態產生 SELECT 欄位清單字串
DECLARE @Cols NVARCHAR(MAX) = N'';
SELECT @Cols += QUOTENAME(name) + N', '
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YourTable')
ORDER BY column_id;
-- 移除尾端逗號
SET @Cols = LEFT(@Cols, LEN(@Cols) - 1);
-- 動態 SQL 範例
DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Cols + N' FROM dbo.YourTable;';
EXEC sp_executesql @SQL;
進階：判斷資料表是否存在
IF OBJECT_ID('dbo.YourTable', 'U') IS NOT NULL
PRINT '資料表存在';
ELSE
PRINT '資料表不存在';
應用場景
- 程式碼產生器（Entity Class、DTO、Repository）
- 動態建立暫存表/同步結構/比對 Schema 差異
- ETL 封裝自動對應來源/目的欄位
- 審計觸發器記錄變更欄位
權限需求
VIEW DEFINITION 權限或 db_ddladmin/db_owner- 跨資料庫查詢需
SELECT 權限於目標資料庫的 sys.columns
