求SQL存储过程：查询或删除全库全表CNUM开头列的指定字符串

以下是针对SQL Server编写的存储过程，完全匹配你的需求：

CREATE PROCEDURE dbo.SearchAndCleanCNUMColumns
    @SearchStr NVARCHAR(MAX),
    @Action BIT -- 1=查询匹配结果, 0=清理指定字符串
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

IF @Action = 1
BEGIN
    -- 生成查询语句，找出所有包含指定字符串的CNUM开头列
    SET @SQL = N'';

    SELECT @SQL = @SQL + N'
    SELECT
        DB_NAME() AS [数据库],
        ''' + t.name + ''' AS [表],
        ''' + c.name + ''' AS [列]
    FROM ' + QUOTENAME(t.name) + '
    WHERE ' + QUOTENAME(c.name) + ' LIKE ''%'' + @SearchStr + ''%''
    UNION ALL'
    FROM
        sys.tables t
        JOIN sys.columns c ON t.object_id = c.object_id
        JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE
        c.name LIKE 'CNUM%'
        AND ty.name IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR'); -- 仅处理字符类型列

    -- 移除末尾多余的UNION ALL并执行查询
    IF @SQL <> N''
    BEGIN
        SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
        EXEC sp_executesql @SQL, N'@SearchStr NVARCHAR(MAX)', @SearchStr;
    END
END
ELSE
BEGIN
    -- 生成批量更新语句，清理所有CNUM开头列中的指定字符串
    SET @SQL = N'';

    SELECT @SQL = @SQL + N'
    UPDATE ' + QUOTENAME(t.name) + '
    SET ' + QUOTENAME(c.name) + ' = REPLACE(' + QUOTENAME(c.name) + ', @SearchStr, '''')
    WHERE ' + QUOTENAME(c.name) + ' LIKE ''%'' + @SearchStr + ''%'';'
    FROM
        sys.tables t
        JOIN sys.columns c ON t.object_id = c.object_id
        JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE
        c.name LIKE 'CNUM%'
        AND ty.name IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR'); -- 仅处理字符类型列

    -- 执行动态更新语句
    IF @SQL <> N''
        EXEC sp_executesql @SQL, N'@SearchStr NVARCHAR(MAX)', @SearchStr;
END
GO

核心细节说明 #

• 参数控制：@SearchStr传入目标字符串，@Action切换执行逻辑（1查询/0清理）
• 查询逻辑：通过系统视图筛选所有CNUM开头的字符类型列，动态拼接查询语句检测列中是否包含目标字符串，最终返回匹配的数据库、表、列信息
• 清理逻辑：生成批量UPDATE语句，用REPLACE函数将目标字符串替换为空，仅更新包含目标字符串的行，避免无意义的全表更新
• 安全防护：使用QUOTENAME处理表名/列名，防止SQL注入；仅处理字符类型列，避免对数值、日期等非字符列执行无效操作

使用示例 #

1. 查询包含字符串'test'的CNUM列信息：

EXEC dbo.SearchAndCleanCNUMColumns @SearchStr = 'test', @Action = 1;

2. 清理所有CNUM列中的字符串'test'：

EXEC dbo.SearchAndCleanCNUMColumns @SearchStr = 'test', @Action = 0;

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