从数据库所有表中查找特定的数据(模糊匹配)

需求：我们想查找某个数据在数据库哪些表中存在

使用存储过程来解决，在所需要查找的数据库下面创建以下存储过程：

-- =============================================

-- Description:    从数据库中查找特定数据所在的所有表
-- =============================================
ALTER PROCEDURE [dbo].[P_find] 
    -- Add the parameters for the stored procedure here
    @DataType INT,--类型：1字符型、2数值型 、3日期型  
    @searchData  NVarChar(200)--需要搜索的内容   
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    --创建临时表存放结果
    IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#resTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[#resTable]
      
    CREATE TABLE #resTable(ID int identity primary key,tblName sysname,colName sysname)  
    
    IF (@DataType <> 1 AND @DataType <> 2 AND @DataType <> 3) GOTO Quit
     
    DECLARE @tblName NVarChar(500),@colName sysname,@sql nvarchar(1000), @type_id NVarChar(100)  
    
    IF @DataType = 1   
            set @type_id='167,175,231,239,35,99'
    ELSE IF @DataType = 2 
         set @type_id='56,48,52,59,60,62,106,108,122'
    ELSE IF @DataType = 3   
          set @type_id='61'
          
    DECLARE tmpCursor cursor fast_forward  
     For   
       Select '['+SCHEMA_NAME(SCHEMA_ID)+'].['++']' tableName,'['++']' columnName 
         From sys.columns c inner join sys.objects o on c.object_id=o.object_id  
         Where o.type_desc='user_table' and CHARINDEX(','+CAST(user_type_id AS nvarchar)+',',','+@type_id+',')>0   
         --Where o.type_desc='user_table' and user_type_id  IN (167,175,231,239,35,99)

    OPEN tmpCursor  
    FETCH next from tmpCursor into @tblName,@colName  
    WHILE @@FETCH_STATUS=0  
    BEGIN  
       SET @sql='if exists (select * from '+@tblName+' where '  
       IF @DataType =1
            set @sql =@sql + @colName + ' like ''%'+@searchData +'%'')'  
       ELSE IF @DataType =2 
           set @sql =@sql + @colName + ' in ('+@searchData+'))'  
       ELSE IF @DataType =3
           set @sql =@sql + 'CONVERT(Varchar(20),'+@colName + ',121) like ''%'+@searchData +'%'')' 
       
       SET @sql = @sql +' INSERT #resTable(tblName,colName) VALUES('''+@tblName+''','''+@colName+''')'  
      ---print @sql   
       EXEC (@sql)  
    FETCH next from tmpCursor into @tblName,@colName  
    END  
    CLOSE tmpCursor   
    DEALLOCATE tmpCursor
  QUIT: 
    IF (SELECT COUNT(*) FROM #resTable) > 0 
    BEGIN
       DECLARE @ls_Table NVarChar(500),@ls_col sysname,@ls_sql nvarchar(1000) 
       DECLARE Cur_DataTable CURSOR FOR SELECT tblName,colName FROM #resTable
       OPEN Cur_DataTable
       FETCH NEXT FROM  Cur_DataTable INTO @ls_Table,@ls_col
       WHILE @@FETCH_STATUS = 0
       BEGIN
           IF @DataType <> 3
           SET @ls_sql = 'select '''+@ls_Table+''+''+@ls_col+'''as 表名字段名,'+@ls_col+' from '+@ls_Table+' where '+@ls_col+' like ''%'+@searchData+'%'''
           ELSE
           SET @ls_sql = 'select '''+@ls_Table+''+''+@ls_col+'''as 表名字段名,'+@ls_col+' from '+@ls_Table+' where convert(VarChar(20),'+@ls_col+',121) like ''%'+@searchData+'%'''
           --print (@ls_sql)
           EXEC (@ls_sql)
       FETCH next from Cur_DataTable into @ls_Table,@ls_col
       END
       CLOSE Cur_DataTable   
       DEALLOCATE Cur_DataTable
       END
END

 

使用方法，执行存储过程：

USE [数据库名]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[P_find]
        @DataType = 1,
        @searchData = N'要查找的文本'

SELECT    'Return Value' = @return_value

GO

 

执行结果如下：