Search All Tables, All Columns for a Specific Value SQL Server

 1
 2-- Set Parameters ------------
 3
 4DECLARE @Search nvarchar(120) = '2008';
 5
 6DECLARE @CheckString      INT = 1;
 7DECLARE @CheckNumber      INT = 0;
 8DECLARE @CheckDateTime    INT = 0;  -- cast(@castDateTime)
 9DECLARE @CheckXml         INT = 0;
10
11DECLARE @ReturnTop        NVARCHAR(50) = 'TOP 100';
12DECLARE @castDateTime     NVARCHAR(50) = 'yyyy/MM/dd HH:mm:ss';  -- yyyy/MM/dd HH:mm:ss
13
14-----------------------
15SET NOCOUNT ON
16PRINT '-- Server Name : ' + @@servername
17PRINT '-- Database : ' + db_name()
18
19BEGIN TRY DROP TABLE #ColumnCast; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
20CREATE TABLE #Columncast ( [Typelist] nvarchar(MAX) , [Columncast] nvarchar(128) , [ColumnWhere] nvarchar(256) );
21IF (@CheckString = 1)   BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|nvarchar|varchar|char|nchar|', '$ColumnName', ' $ColumnCast LIKE ''%$Search%''' ) END;
22IF (@CheckNumber = 1)   BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|int|smallint|real|bigint|bigint|tinyint|float|bit|decimal|numeric|', 'CAST($ColumnName AS NVARCHAR(128))', ' $ColumnCast = ''$Search''' ) END;
23IF (@CheckDateTime = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|datetime|time|smalldatetime|', 'FORMAT($ColumnName, '''+@castDateTime+''')', ' $ColumnCast = ''$Search''' ) END;
24IF (@CheckXml = 1)      BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|xml|', 'CAST($ColumnName AS NVARCHAR(MAX))', ' $ColumnCast LIKE ''%$Search%''' ) END;
25
26BEGIN TRY DROP TABLE #Tables; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
27CREATE TABLE #Tables ( TableId int IDENTITY(1,1) NOT NULL, TableName nvarchar(512) , ColumnName nvarchar(256) , ColumnWhere nvarchar(256) , ColumnCast nvarchar(512), SqlScript nvarchar(max) );
28
29DECLARE @Template nvarchar(max) = 'SELECT $ReturnTop ''$TableName'' as TableName, ''$ColumnName'' as ColumnName, $Columncast as ColumnValue FROM $TableName with(nolock) WHERE $ColumnWhere';
30
31DECLARE @CollateDatabase nvarchar(100);
32-- Fix Collation bug
33SELECT @CollateDatabase = collation_name  FROM sys.databases  where [name] = db_name();
34--SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX('|'+t.name COLLATE SQL_Latin1_General_CP1_CI_AI +'|', p.[TypeList]) > 0;
35DECLARE @TableTemplate nvarchar(max) = 'SELECT QUOTENAME(s.name) + ''.'' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX(''|''+t.name COLLATE ' + @CollateDatabase + ' +''|'', p.[TypeList]) > 0'
36PRINT @TableTemplate
37
38INSERT INTO #Tables ( TableName, ColumnName, ColumnCast, ColumnWhere )
39EXEC (@TableTemplate)
40
41UPDATE #Tables SET SqlScript = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Template, '$TableName', TableName),'$ColumnWhere', ColumnWhere),'$Columncast',ColumnCast),'$ColumnName', ColumnName), '$Search', @Search),'$ReturnTop', @ReturnTop);
42-- select * from #Tables;
43
44BEGIN TRY DROP TABLE #Results; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
45CREATE TABLE #Results (ResultId int IDENTITY(1,1) NOT NULL, TableName nvarchar(256) , ColumnName nvarchar(128) , ColumnValue nvarchar(MAX), SqlScript nvarchar(MAX) );
46DECLARE @TableName nvarchar(256) , @ColumnName nvarchar(128) , @SqlScript nvarchar(MAX) , @TableId int,@SqlScriptCode NVARCHAR(max), @LastResultId INT;
47WHILE (exists(select * from #Tables))
48BEGIN
49    SELECT @ColumnName = '', @TableName = '', @SqlScript = '';
50    SELECT TOP 1 @TableId = TableId , @TableName = TableName , @ColumnName = ColumnName , @SqlScript = SqlScript FROM #Tables;
51    DELETE FROM #Tables WHERE TableId = @TableId;
52    SET @SqlScriptCode = REPLACE(@SqlScript, '''', '''''');
53    SET @SqlScript = REPLACE(@sqlScript , '$SqlScriptCode', @SqlScriptCode);
54    print @SqlScript
55
56    INSERT INTO #Results (TableName, ColumnName, ColumnValue)
57    EXEC (@SqlScript);
58
59    SET @SqlScript = REPLACE (@SqlScript, ' FROM', ', * FROM');
60    UPDATE #Results SET SqlScript = @SqlScript WHERE SqlScript IS NULL;
61
62END
63SELECT DISTINCT @@servername as [Servername], db_name() as DatabaseName, TableName, ColumnName, ColumnValue, SqlScript FROM #Results;
64DROP TABLE #Results;
65go