动态表名字段验证：寻求高效可复用的SQL实现方案

针对你遇到的动态表数据验证难题，我结合T-SQL的最佳实践整理了几个高效且易维护的方案，完美适配你这种「固定结构+动态数量」的表场景：

方案1：元数据驱动的动态验证脚本（兼顾性能与可维护性） #

核心思路是：利用系统视图自动识别所有foo_*和bar_*表，同时把两类表的固定验证规则写成模板，动态拼接成SQL执行。这样既避免了硬编码N值，又把业务规则和动态遍历逻辑分离，大幅降低维护成本。

第一步：创建统一的错误日志表（可选但推荐） #

先建一个表来存储所有验证错误，方便后续排查：

CREATE TABLE ValidationErrors (
    ErrorId INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128) NOT NULL,
    RecordId INT NOT NULL,
    ErrorMessage NVARCHAR(255) NOT NULL,
    ErrorTime DATETIME DEFAULT GETDATE()
)

第二步：编写存储过程实现动态验证 #

这个存储过程会自动遍历所有目标表，执行对应规则的验证，并把错误写入日志表：

CREATE OR ALTER PROCEDURE ValidateDynamicTables
AS
BEGIN
    SET NOCOUNT ON;

    -- 处理foo类表：验证some_foo_data_1长度等规则
    DECLARE @tables TABLE (TableName NVARCHAR(128));
    INSERT INTO @tables
    SELECT name FROM sys.tables WHERE name LIKE 'foo[_]%';

    DECLARE @currentTable NVARCHAR(128), @validationSql NVARCHAR(MAX);
    DECLARE tableCursor CURSOR FOR SELECT TableName FROM @tables;

    OPEN tableCursor;
    FETCH NEXT FROM tableCursor INTO @currentTable;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接foo类表的验证SQL
        SET @validationSql = N'
            INSERT INTO ValidationErrors (TableName, RecordId, ErrorMessage)
            SELECT 
                ''' + @currentTable + ''',
                id,
                ''some_foo_data_1 must be 2-70 characters''
            FROM ' + QUOTENAME(@currentTable) + '
            WHERE LEN(some_foo_data_1) NOT BETWEEN 2 AND 70
            UNION ALL
            -- 可添加其他foo类字段的验证规则
            SELECT 
                ''' + @currentTable + ''',
                id,
                ''some_foo_data_2 cannot be null''
            FROM ' + QUOTENAME(@currentTable) + '
            WHERE some_foo_data_2 IS NULL;
        ';

        EXEC sp_executesql @validationSql;
        FETCH NEXT FROM tableCursor INTO @currentTable;
    END

    CLOSE tableCursor;
    DEALLOCATE tableCursor;

    -- 处理bar类表：验证some_bar_data_1的decimal范围等规则
    TRUNCATE TABLE @tables;
    INSERT INTO @tables
    SELECT name FROM sys.tables WHERE name LIKE 'bar[_]%';

    OPEN tableCursor;
    FETCH NEXT FROM tableCursor INTO @currentTable;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接bar类表的验证SQL
        SET @validationSql = N'
            INSERT INTO ValidationErrors (TableName, RecordId, ErrorMessage)
            SELECT 
                ''' + @currentTable + ''',
                id,
                ''some_bar_data_1 must be a decimal between 0 and 100''
            FROM ' + QUOTENAME(@currentTable) + '
            WHERE TRY_CAST(some_bar_data_1 AS DECIMAL(18,2)) IS NULL
                OR some_bar_data_1 < 0 
                OR some_bar_data_1 > 100
            UNION ALL
            -- 可添加其他bar类字段的验证规则
            SELECT 
                ''' + @currentTable + ''',
                id,
                ''some_bar_data_2 must be a valid date''
            FROM ' + QUOTENAME(@currentTable) + '
            WHERE ISDATE(some_bar_data_2) = 0;
        ';

        EXEC sp_executesql @validationSql;
        FETCH NEXT FROM tableCursor INTO @currentTable;
    END

    CLOSE tableCursor;
    DEALLOCATE tableCursor;
END

方案2：动态生成验证视图（复用性更强） #

如果需要频繁查询验证结果，可以为每个动态表生成对应的验证视图。视图的结构固定，后续直接查询视图就能获取错误记录，非常直观：

CREATE OR ALTER PROCEDURE CreateValidationViews
AS
BEGIN
    SET NOCOUNT ON;

    -- 为foo类表生成验证视图
    DECLARE @tables TABLE (TableName NVARCHAR(128));
    INSERT INTO @tables
    SELECT name FROM sys.tables WHERE name LIKE 'foo[_]%';

    DECLARE @currentTable NVARCHAR(128), @createViewSql NVARCHAR(MAX);
    DECLARE tableCursor CURSOR FOR SELECT TableName FROM @tables;

    OPEN tableCursor;
    FETCH NEXT FROM tableCursor INTO @currentTable;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @createViewSql = N'
            CREATE OR ALTER VIEW vw_' + @currentTable + '_Validation
            AS
            SELECT 
                id,
                some_foo_data_1,
                CASE WHEN LEN(some_foo_data_1) NOT BETWEEN 2 AND 70 THEN ''Invalid length (2-70 required)'' ELSE NULL END AS some_foo_data_1_Error,
                some_foo_data_2,
                CASE WHEN some_foo_data_2 IS NULL THEN ''Cannot be null'' ELSE NULL END AS some_foo_data_2_Error
            FROM ' + QUOTENAME(@currentTable) + ';
        ';

        EXEC sp_executesql @createViewSql;
        FETCH NEXT FROM tableCursor INTO @currentTable;
    END

    CLOSE tableCursor;
    DEALLOCATE tableCursor;

    -- 为bar类表生成验证视图
    TRUNCATE TABLE @tables;
    INSERT INTO @tables
    SELECT name FROM sys.tables WHERE name LIKE 'bar[_]%';

    OPEN tableCursor;
    FETCH NEXT FROM tableCursor INTO @currentTable;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @createViewSql = N'
            CREATE OR ALTER VIEW vw_' + @currentTable + '_Validation
            AS
            SELECT 
                id,
                some_bar_data_1,
                CASE 
                    WHEN TRY_CAST(some_bar_data_1 AS DECIMAL(18,2)) IS NULL THEN ''Not a valid decimal''
                    WHEN some_bar_data_1 < 0 OR some_bar_data_1 > 100 THEN ''Must be between 0 and 100''
                    ELSE NULL 
                END AS some_bar_data_1_Error,
                some_bar_data_2,
                CASE WHEN ISDATE(some_bar_data_2) = 0 THEN ''Not a valid date'' ELSE NULL END AS some_bar_data_2_Error
            FROM ' + QUOTENAME(@currentTable) + ';
        ';

        EXEC sp_executesql @createViewSql;
        FETCH NEXT FROM tableCursor INTO @currentTable;
    END

    CLOSE tableCursor;
    DEALLOCATE tableCursor;
END

性能与维护优化技巧 #

1. 规则模板化：把验证规则单独提取成字符串变量，比如@FooValidationRules，修改规则时只需要更新模板，不需要改动遍历逻辑，进一步提升可维护性。
2. 避免行级函数开销：尽量用集合式验证（比如直接在WHERE子句中写条件），替代标量函数的行级调用，这比你之前用函数的方案性能提升明显。
3. 批量处理：如果N很大，可以把表分成批次处理，避免单次执行占用过多资源。
4. 验证类型安全：用TRY_CAST/TRY_CONVERT替代ISNUMERIC，避免误判（比如ISNUMERIC('123,45')会返回1，但TRY_CAST能准确识别decimal格式）。

内容的提问来源于stack exchange，提问作者Westerlund.io