SQL Server 新增欄位並加入說明：擴展屬性完整實作

適用版本

SQL Server 2005+（含 2005、2008、2012、2016、2019）

預存程式：SetColumnInfo

CREATE PROCEDURE [dbo].[SetColumnInfo]
    @TableName      NVARCHAR(100),
    @ColumnName     NVARCHAR(100),
    @ColumnInfo     NVARCHAR(2000),
    @ColumnType     NVARCHAR(100) = 'NVARCHAR(50)',
    @ColumnDefault  NVARCHAR(100) = 'NULL'
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 若欄位不存在則新增
    IF NOT EXISTS (
        SELECT 1 FROM SysColumns 
        WHERE Id = OBJECT_ID(@TableName) AND Name = @ColumnName
    )
    BEGIN
        DECLARE @Sql NVARCHAR(MAX) = 
            'ALTER TABLE ' + QUOTENAME(@TableName) + 
            ' ADD ' + QUOTENAME(@ColumnName) + ' ' + @ColumnType + ' ' + @ColumnDefault;
        PRINT 'Exec: ' + @Sql;
        PRINT 'Add[' + @ColumnName + ']Column';
        EXEC (@Sql);
    END

    -- 2. 管理擴展屬性（MS_Description）
    IF EXISTS (
        SELECT 1 FROM ::Fn_ListExtendedProperty(
            'MS_Description', 'SCHEMA', N'Dbo', 'TABLE', @TableName, 'COLUMN', @ColumnName
        )
    )
    BEGIN
        PRINT 'Edit[' + @ColumnName + ']Description';
        EXEC Sys.Sp_UpdateExtendedProperty
            @Name = N'MS_Description', @Value = @ColumnInfo,
            @Level0Type = N'SCHEMA', @Level0Name = N'Dbo',
            @Level1Type = N'TABLE', @Level1Name = @TableName,
            @Level2Type = N'COLUMN', @Level2Name = @ColumnName;
    END
    ELSE
    BEGIN
        PRINT 'Add[' + @ColumnName + ']Description';
        EXEC Sys.Sp_AddExtendedProperty
            @Name = N'MS_Description', @Value = @ColumnInfo,
            @Level0Type = N'SCHEMA', @Level0Name = N'Dbo',
            @Level1Type = N'TABLE', @Level1Name = @TableName,
            @Level2Type = N'COLUMN', @Level2Name = @ColumnName;
    END
END;
GO

核心系統物件解析

SysColumns：檢查欄位存在性

SELECT * FROM SysColumns WHERE Id = OBJECT_ID('TableName') AND Name = 'ColumnName';

ALTER TABLE：欄位增刪改

-- 新增
ALTER TABLE TableName ADD ColumnName DataType DefaultValue;
-- 刪除
ALTER TABLE TableName DROP COLUMN ColumnName;
-- 修改型別
ALTER TABLE TableName ALTER COLUMN ColumnName NewDataType;

擴展屬性管理

刪除說明範例

EXEC Sp_DropExtendedProperty 
    'MS_Description', 'User', 'Dbo', 'Table', 'TableName', 'Column', 'ColumnName';

呼叫範例

EXEC SetColumnInfo 
    'Orders', 'CustomerNote', N'客戶備註：記錄特殊需求', 'NVARCHAR(500)', 'NULL';

圖示說明

總結

透過 SysColumns 檢查欄位、ALTER TABLE 動態新增、Sp_Add/UpdateExtendedProperty 管理 MS_Description，實現「欄位與說明同步維護」的自動化流程，適用於資料字典自動化、文件生成等場景。