Search all tables, all columns for a specific value SQL Server

Rédigé par M Sozezzo - - Aucun commentaire

This script* search for a specific value that you can select sql type:

  • String - %my string%
  • Number - exact number
  • Date - exact format yyyy/MM/dd HH:mm:ss 
  • XML - %my string%

You can limit how many result by tables using "TOP n"

You must select database to run this script, and avoid using production environment.

* Tested with Sql Server 2012, 2014 and 2016

 

-- Set Parameters ------------

DECLARE @Search nvarchar(120) = '2008';

DECLARE @CheckString      INT = 1;
DECLARE @CheckNumber      INT = 0;
DECLARE @CheckDateTime    INT = 0;  -- cast(@castDateTime)   
DECLARE @CheckXml         INT = 0;


DECLARE @ReturnTop        NVARCHAR(50) = 'TOP 100';
DECLARE @castDateTime     NVARCHAR(50) = 'yyyy/MM/dd HH:mm:ss';  -- yyyy/MM/dd HH:mm:ss

-----------------------
SET NOCOUNT ON
PRINT '-- Server Name : ' + @@servername
PRINT '-- Database : ' + db_name()

BEGIN 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;
CREATE TABLE #Columncast ( [Typelist] nvarchar(MAX) , [Columncast] nvarchar(128) , [ColumnWhere] nvarchar(256) );
IF (@CheckString = 1)   BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|nvarchar|varchar|char|nchar|', '$ColumnName', ' $ColumnCast LIKE ''%$Search%''' ) END;
IF (@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;
IF (@CheckDateTime = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|datetime|time|smalldatetime|', 'FORMAT($ColumnName, '''+@castDateTime+''')', ' $ColumnCast = ''$Search''' ) END;
IF (@CheckXml = 1)      BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|xml|', 'CAST($ColumnName AS NVARCHAR(MAX))', ' $ColumnCast LIKE ''%$Search%''' ) END;

BEGIN 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;
CREATE TABLE #Tables ( TableId int IDENTITY(1,1) NOT NULL, TableName nvarchar(512) , ColumnName nvarchar(256) , ColumnWhere nvarchar(256) , ColumnCast nvarchar(512), SqlScript nvarchar(max) );

DECLARE @Template nvarchar(max) = 'SELECT $ReturnTop ''$TableName'' as TableName, ''$ColumnName'' as ColumnName, $Columncast as ColumnValue FROM $TableName with(nolock) WHERE $ColumnWhere';

DECLARE @CollateDatabase nvarchar(100);
-- Fix Collation bug
SELECT @CollateDatabase = collation_name  FROM sys.databases  where [name] = db_name();
--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;
DECLARE @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'
PRINT @TableTemplate

INSERT INTO #Tables ( TableName, ColumnName, ColumnCast, ColumnWhere )
EXEC (@TableTemplate)

UPDATE #Tables SET SqlScript = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Template, '$TableName', TableName),'$ColumnWhere', ColumnWhere),'$Columncast',ColumnCast),'$ColumnName', ColumnName), '$Search', @Search),'$ReturnTop', @ReturnTop);
-- select * from #Tables;

BEGIN 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;
CREATE TABLE #Results (ResultId int IDENTITY(1,1) NOT NULL, TableName nvarchar(256) , ColumnName nvarchar(128) , ColumnValue nvarchar(MAX), SqlScript nvarchar(MAX) );
DECLARE @TableName nvarchar(256) , @ColumnName nvarchar(128) , @SqlScript nvarchar(MAX) , @TableId int,@SqlScriptCode NVARCHAR(max), @LastResultId INT;
WHILE (exists(select * from #Tables))
BEGIN
    SELECT @ColumnName = '', @TableName = '', @SqlScript = '';
    SELECT TOP 1 @TableId = TableId , @TableName = TableName , @ColumnName = ColumnName , @SqlScript = SqlScript FROM #Tables;
    DELETE FROM #Tables WHERE TableId = @TableId;
    SET @SqlScriptCode = REPLACE(@SqlScript, '''', '''''');
    SET @SqlScript = REPLACE(@sqlScript , '$SqlScriptCode', @SqlScriptCode);
    print @SqlScript

    INSERT INTO #Results (TableName, ColumnName, ColumnValue)
    EXEC (@SqlScript);

    SET @SqlScript = REPLACE (@SqlScript, ' FROM', ', * FROM');
    UPDATE #Results SET SqlScript = @SqlScript WHERE SqlScript IS NULL;

END
SELECT DISTINCT @@servername as [Servername], db_name() as DatabaseName, TableName, ColumnName, ColumnValue, SqlScript FROM #Results;
DROP TABLE #Results;
go

 

Source: (Old versions)  http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated/

Garbage : https://goo.gl/iDHkk

 

 

Fil RSS des articles