SQL Server脚本开发：为tbl表自动添加col(n+1)至col50列

完整脚本 #

-- 1. 获取tbl表中col开头列的最大序号
DECLARE @max_col_num INT;

SELECT @max_col_num = MAX(CAST(SUBSTRING(name, 4, LEN(name)-3) AS INT))
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.tbl') 
AND name LIKE 'col[0-9]%';

-- 处理表中无任何col开头列的情况
IF @max_col_num IS NULL
    SET @max_col_num = 0;

-- 2. 批量添加从col(@max_col_num+1)到col50的列
DECLARE @current_num INT = @max_col_num + 1;
DECLARE @col_name NVARCHAR(50);
DECLARE @sql NVARCHAR(MAX);

WHILE @current_num <= 50
BEGIN
    SET @col_name = 'col' + CAST(@current_num AS NVARCHAR(10));
    
    -- 检查列是否已存在
    IF NOT EXISTS (
        SELECT 1 
        FROM sys.columns 
        WHERE object_id = OBJECT_ID('dbo.tbl') 
        AND name = @col_name
    )
    BEGIN
        -- 生成添加列的动态SQL
        SET @sql = 'ALTER TABLE dbo.tbl ADD ' + QUOTENAME(@col_name) + ' VARCHAR(100) NULL;';
        EXEC sp_executesql @sql;
    END
    
    SET @current_num = @current_num + 1;
END

关键说明 #

• 存在性判断：通过sys.columns系统视图查询目标列是否存在，比COL_LENGTH函数更直观，也能兼容复杂列名场景
• 动态SQL安全：使用QUOTENAME包裹列名，避免潜在的SQL注入风险（即使本场景中列名是固定格式，这也是最佳实践）
• 边界处理：增加了表中无任何col开头列的判断，此时会从col1开始添加到col50
• 循环逻辑：仅添加当前最大序号到50之间缺失的列，不会重复添加已存在的列

内容的提问来源于stack exchange，提问作者Priya Bangwal