SQL Server中删除指定表的所有空列（未知列名场景）

如果需要删除某张未知列名的表中所有全为NULL的列，核心思路是先识别出这些空列，再生成对应的删除语句。以下是主流数据库的实现方案：

MySQL 实现 #

-- 替换为你的库名和表名
SET @table_name = 'table1';
SET @schema_name = 'your_database';

-- 生成检查各列非NULL值数量的SQL
SELECT GROUP_CONCAT(
    CONCAT('SELECT ''', column_name, ''' AS column_name, COUNT(', column_name, ') AS non_null_count FROM ', @schema_name, '.', @table_name)
    SEPARATOR ' UNION ALL '
) INTO @check_sql FROM information_schema.columns 
WHERE table_schema = @schema_name AND table_name = @table_name;

-- 获取所有全为NULL的列
SET @drop_columns = (
    SELECT GROUP_CONCAT(column_name SEPARATOR ', ') FROM (
        @check_sql
    ) AS t WHERE non_null_count = 0
);

-- 生成并执行删除语句
SET @drop_sql = IF(@drop_columns IS NOT NULL, CONCAT('ALTER TABLE ', @schema_name, '.', @table_name, ' DROP COLUMN ', @drop_columns), 'SELECT "No columns to drop" AS message;');

PREPARE stmt FROM @drop_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现 #

DECLARE @table_name NVARCHAR(128) = 'table1';
DECLARE @schema_name NVARCHAR(128) = 'dbo';
DECLARE @drop_columns NVARCHAR(MAX);
DECLARE @check_sql NVARCHAR(MAX);

-- 生成检查各列非NULL值数量的SQL
SELECT @check_sql = STRING_AGG(
    CONCAT('SELECT ''', COLUMN_NAME, ''' AS column_name, COUNT(', COLUMN_NAME, ') AS non_null_count FROM ', @schema_name, '.', @table_name),
    ' UNION ALL '
) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name;

-- 获取所有全为NULL的列
SELECT @drop_columns = STRING_AGG(column_name, ', ') FROM (
    EXEC sp_executesql @check_sql
) AS t WHERE non_null_count = 0;

-- 执行删除（若有需要删除的列）
IF @drop_columns IS NOT NULL
BEGIN
    DECLARE @drop_sql NVARCHAR(MAX) = CONCAT('ALTER TABLE ', @schema_name, '.', @table_name, ' DROP COLUMN ', @drop_columns);
    EXEC sp_executesql @drop_sql;
END
ELSE
BEGIN
    PRINT 'No columns to drop';
END

PostgreSQL 实现 #

DO $$
DECLARE
    table_name TEXT := 'table1';
    schema_name TEXT := 'public';
    drop_columns TEXT;
    check_sql TEXT;
BEGIN
    -- 生成检查各列非NULL值数量的SQL
    SELECT string_agg(
        format('SELECT ''%I'' AS column_name, COUNT(%I) AS non_null_count FROM %I.%I', column_name, column_name, schema_name, table_name),
        ' UNION ALL '
    ) INTO check_sql FROM information_schema.columns 
    WHERE table_schema = schema_name AND table_name = table_name;

    -- 获取所有全为NULL的列
    SELECT string_agg(column_name, ', ') INTO drop_columns FROM (
        EXECUTE check_sql
    ) AS t WHERE non_null_count = 0;

    -- 执行删除（若有需要删除的列）
    IF drop_columns IS NOT NULL THEN
        EXECUTE format('ALTER TABLE %I.%I DROP COLUMN %s', schema_name, table_name, drop_columns);
    ELSE
        RAISE NOTICE 'No columns to drop';
    END IF;
END $$;

注意事项 #

• 操作前务必备份数据，防止误删重要列
• 若需保留特定列（如示例中的Id），可在查询information_schema.columns时添加过滤条件，例如AND column_name != 'Id'
• 不同数据库的系统函数（如列拼接函数）存在差异，需对应调整
• 执行动态SQL需具备足够的数据库权限

内容的提问来源于stack exchange，提问作者Marie Cécile MAOUI