sqlserver查询所有用户创建的表中所有字段包含特定字符的数据

查询所有用户创建的表中所有字段包含特定字符的数据

数据库：sqlserver

方式：存储过程

创建存储过程

USE [DBDate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--创建存储过程
CREATE PROCEDURE [dbo].[FindDataWithPatternInColumns]  
    @SearchPattern NVARCHAR(50)  
AS  
BEGIN  
    SET NOCOUNT ON;  
    --定义表变量，存储查询结果
	DECLARE @MyResults TABLE (
		TableName NVARCHAR(256),  
		ColumnName NVARCHAR(256), 
		Id uniqueidentifier,  
		MatchedData NVARCHAR(MAX)  
	);  
  
    DECLARE @tableName NVARCHAR(256)  
    DECLARE @columnName NVARCHAR(256)  
    DECLARE @sql NVARCHAR(MAX)  
    --定义游标循环所有表和字段
    DECLARE column_cursor CURSOR FOR   
        SELECT TABLE_NAME, COLUMN_NAME   
        FROM INFORMATION_SCHEMA.COLUMNS   
        WHERE TABLE_CATALOG = DB_NAME(DB_ID()) AND TABLE_SCHEMA = 'dbo' -- 假设所有表都在dbo架构下  
          AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar', 'text', 'ntext') -- 只考虑字符类型字段  
    --打开游标
    OPEN column_cursor  
    FETCH NEXT FROM column_cursor INTO @tableName, @columnName  
    --循环游标
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        SET @sql = ' SELECT '''+QUOTENAME(@tableName)+''' as TableName, '''+QUOTENAME(@columnName)+''' as ColumnName,id,'+QUOTENAME(@columnName)+' as MatchedData  FROM ' + QUOTENAME(@tableName) +   
                   ' WHERE ' + QUOTENAME(@columnName) + ' LIKE ''%' + REPLACE(@SearchPattern, '%', '\%') + '%'''  
        insert into @MyResults EXEC sp_executesql @sql  
  
        FETCH NEXT FROM column_cursor INTO @tableName, @columnName  
    END  
    --关闭释放游标
    CLOSE column_cursor  
    DEALLOCATE column_cursor  
    --查询返回结果
	select * from @MyResults
END  
GO

使用示例：查询所有表包含“公司”的所有字段

EXEC [dbo].[FindDataWithPatternInColumns] @SearchPattern = N'公司'