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

SQL Server 取得資料表所有欄位名稱的預存程序：syscolumns 系統表查詢

編輯：關於SqlServer

功能說明

輸入資料表名稱，回傳該表所有欄位名稱以逗號分隔的字串，適用於動態 SQL 拼裝、程式碼產生器等場景。

預存程序代碼

CREATE PROCEDURE pAllColumnSql 
    @tableName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Name VARCHAR(2000) = '';

    SELECT @Name = @Name + ',' + CAST(name AS VARCHAR)
    FROM syscolumns
    WHERE id = OBJECT_ID(@tableName);

    -- 移除前導逗號
    SET @Name = SUBSTRING(@Name, 2, LEN(@Name) - 1);

    SELECT @Name AS ColumnList;
END;
GO

使用範例

EXEC pAllColumnSql 'dbo.Orders';
-- 輸出: OrderID,CustomerID,OrderDate,TotalAmount,Status

現代化改寫建議 (SQL Server 2005+)

syscolumns 為相容性視圖，建議改用 sys.columns：

SELECT @Name = @Name + ',' + name
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableName);

進階版：回傳欄位清單資料表 (含型別、長度、是否可空)

CREATE PROCEDURE usp_GetTableColumns
    @tableName NVARCHAR(128),
    @schemaName NVARCHAR(128) = 'dbo'
AS
BEGIN
    SELECT 
        c.name AS ColumnName,
        t.name AS DataType,
        c.max_length AS MaxLength,
        c.precision AS Precision,
        c.scale AS Scale,
        c.is_nullable AS IsNullable,
        c.is_identity AS IsIdentity
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName))
    ORDER BY c.column_id;
END;