Post
Ask Question

Find Any String from a Database in SQL Server

In SQL Server sometimes we get some data for which we are not sure that from which table it is coming the we can use the below stored procedure to find it.

It has 3 input parameters:

  • @DataToSearch: Data which you want to search.
  • @DataToReplace: Data with which you want to replace.
  • @IsExactMatch: Search for exact matching or search any mathing data. If you want to search any matching data you can leave it blank.

This stored procedure does not alter any value in database it only displays the list of table with column name which have the specified data. the @DataToReplace has value the it will show you an script to replace the value which you need to execute separately.

  1. CREATE PROCEDURE FindAnyStringFromTheDataBase  
  2.     @DataToSearch NVARCHAR(4000),  
  3.     @DataToReplace NVARCHAR(4000),  
  4.     @IsExactMatch BIT = 0  
  5. AS  
  6. SET NOCOUNT ON  
  7.    
  8. DECLARE @TempTable TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), IsDataFound BIT)  
  9.    
  10.     INSERT  INTO @TempTable(TableName,SchemaName, ColumnName, DataType)  
  11.     SELECT  Col.Table_Name,Col.TABLE_SCHEMA, Col.Column_Name, Col.Data_Type  
  12.     FROM    Information_Schema.Columns AS Col  
  13.             INNER Join Information_Schema.Tables AS Tbl  
  14.                 ON Col.Table_Name = Tbl.Table_Name  
  15.         AND Col.TABLE_SCHEMA = Tbl.TABLE_SCHEMA  
  16.     WHERE   Table_Type = 'Base Table'  
  17.             And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')  
  18.    
  19.    
  20. DECLARE @i INT  
  21. DECLARE @MAXValue INT  
  22. DECLARE @TableName sysname  
  23. DECLARE @ColumnName sysname  
  24. DECLARE @SchemaName sysname  
  25. DECLARE @SQL NVARCHAR(4000)  
  26. DECLARE @PARAMETERS NVARCHAR(4000)  
  27. DECLARE @IsDataExists BIT  
  28. DECLARE @SQLTemplate NVARCHAR(4000)  
  29. DECLARE @dbName VARCHAR(100)  
  30.    
  31. SELECT  @SQLTemplate = CASE WHEN @IsExactMatch = 1  
  32.                             THEN 'If Exists(Select *  
  33.                                            From   ReplaceTableName  
  34.                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])  
  35.                                                         = ''' + @DataToSearch + '''  
  36.                                            )  
  37.                                       Set @IsDataExists = 1  
  38.                                   Else  
  39.                                       Set @IsDataExists = 0'  
  40.                             ELSE 'If Exists(Select *  
  41.                                            From   ReplaceTableName  
  42.                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])  
  43.                                                         Like ''%' + @DataToSearch + '%''  
  44.                                            )  
  45.                                       Set @IsDataExists = 1  
  46.                                   Else  
  47.                                       Set @IsDataExists = 0'  
  48.                             END,  
  49.         @PARAMETERS = '@IsDataExists Bit OUTPUT',  
  50.         @i = 1  
  51.    
  52. SELECT @i = 1, @MAXValue = MAX(RowId)  
  53. FROM   @TempTable  
  54.    
  55. WHILE @i <= @MAXValue  
  56.     BEGIN  
  57.         SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)  
  58.         FROM    @TempTable  
  59.         WHERE   RowId = @i  
  60.    
  61.    
  62.         PRINT @SQL  
  63.         EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @IsDataExists = @IsDataExists OUTPUT  
  64.    
  65.         IF @IsDataExists =1  
  66.             UPDATE @TempTable SET IsDataFound = 1 WHERE RowId = @i  
  67.    
  68.         SET @i = @i + 1  
  69.     END  
  70.    
  71. SET @dbName = DB_NAME()   
  72.    
  73. SELECT  SchemaName,@dbName as DB,TableName, ColumnName,  
  74.         'Update '+ @dbName+'.'+SchemaName+'.'+TableName+ ' SET '+ColumnName+ ' = replace('+ColumnName+','''+ @DataToSearch+''','''+@DataToReplace+''')' as Script  
  75. FROM    @TempTable  
  76. WHERE   IsDataFound = 1  
  77.   
  78. GO