SQL Server 表結構變更實戰：增刪改列、判斷存在性、重命名與主鍵自動判斷腳本

基礎 DDL 操作

-- 增加列
ALTER TABLE DocDsp ADD DspCode CHAR(200);

-- 刪除列
ALTER TABLE TableName DROP COLUMN ColumnName;

-- 修改列類型/長度/可空
ALTER TABLE TableName ALTER COLUMN ColumnName NVARCHAR(100) NOT NULL;

-- 重命名表/列/索引/約束
EXEC sp_rename 'Schema.TableName', 'NewTableName', 'OBJECT';
EXEC sp_rename 'Schema.TableName.OldCol', 'NewCol', 'COLUMN';
EXEC sp_rename 'Schema.TableName.OldIndex', 'NewIndex', 'INDEX';

判斷表/列存在性再操作（冪等腳本核心）

-- 判斷表存在則刪除 (用戶表)
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TableName') AND type = 'U')
    DROP TABLE dbo.TableName;

-- 判斷臨時表存在則刪除
IF OBJECT_ID('tempdb..##GlobalTemp') IS NOT NULL
    DROP TABLE ##GlobalTemp;

-- 判斷列存在
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('TableName') AND name = 'ColumnName')
BEGIN
    PRINT '列已存在';
    -- ALTER TABLE ... DROP COLUMN ColumnName; -- 或修改
END

智能添加主鍵/普通列（根據表是否已有主鍵）

IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('dbo.TableName') AND type = 'PK')
BEGIN
    PRINT '表已有主鍵，添加普通列';
    ALTER TABLE dbo.TableName ADD NewCol INT DEFAULT 0;
END
ELSE
BEGIN
    PRINT '表無主鍵，添加主鍵列';
    ALTER TABLE dbo.TableName ADD NewCol INT PRIMARY KEY DEFAULT 0;
END

獲取字段描述視圖（復用）

CREATE VIEW dbo.v_FieldDesc AS
SELECT
    o.name AS TableName,
    c.name AS ColumnName,
    CONVERT(NVARCHAR(MAX), ep.value) AS Description,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.objects o ON o.object_id = c.object_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = o.object_id
   AND ep.minor_id = c.column_id
   AND ep.name = 'MS_Description'
   AND ep.class = 1
WHERE o.type = 'U';

sp_rename 參數速查