生成动态SQL：统计STR_前缀staging表指定Status列的行数

针对数据库中所有以STR_开头的表，统计user_status='Active'的行数，以下是不同数据库系统的动态SQL实现方案，替代静态UNION ALL写法：

SQL Server 实现 #

通过系统视图筛选符合条件的表，自动拼接查询语句：

DECLARE @DynamicSQL NVARCHAR(MAX) = ''

SELECT @DynamicSQL = @DynamicSQL + 
'UNION ALL
SELECT ''' + t.name + ''' AS [Table], COUNT(*) AS Rowcount 
FROM ' + QUOTENAME(t.name) + ' 
WHERE user_status = ''Active'''
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name LIKE 'STR_%'
AND c.name = 'user_status'

-- 移除开头多余的UNION ALL
SET @DynamicSQL = STUFF(@DynamicSQL, 1, 10, '')

-- 执行生成的SQL
EXEC sp_executesql @DynamicSQL

MySQL 实现 #

利用information_schema元数据视图生成动态语句：

SET @DynamicSQL = '';

SELECT GROUP_CONCAT(
    'SELECT ''', TABLE_NAME, ''' AS `Table`, COUNT(*) AS Rowcount FROM ', TABLE_NAME, ' WHERE user_status = ''Active'''
    SEPARATOR ' UNION ALL '
) INTO @DynamicSQL
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() -- 限定当前数据库
AND TABLE_NAME LIKE 'STR_%'
AND COLUMN_NAME = 'user_status';

-- 执行动态SQL
PREPARE stmt FROM @DynamicSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Oracle 实现 #

通过ALL_TAB_COLUMNS视图拼接查询语句：

DECLARE
    v_dynamic_sql VARCHAR2(32767);
BEGIN
    SELECT LISTAGG(
        'SELECT ''' || TABLE_NAME || ''' AS "Table", COUNT(*) AS Rowcount FROM ' || TABLE_NAME || ' WHERE user_status = ''Active'''
        , ' UNION ALL '
    ) WITHIN GROUP (ORDER BY TABLE_NAME)
    INTO v_dynamic_sql
    FROM ALL_TAB_COLUMNS
    WHERE OWNER = USER -- 限定当前用户下的表
    AND TABLE_NAME LIKE 'STR_%'
    AND COLUMN_NAME = 'USER_STATUS';

    -- 执行生成的SQL
    EXECUTE IMMEDIATE v_dynamic_sql;
END;
/

注意事项 #

• 确保执行账号拥有所有目标表的查询权限
• 若表名包含特殊字符，需使用对应数据库的转义方式（如SQL Server的QUOTENAME、MySQL的反引号）
• 可根据实际需求调整筛选条件（比如指定特定数据库、用户）

内容的提问来源于stack exchange，提问作者Kittu SD