Search String in SQLServer in All databases all tables all columns

 Search String in SQLServer in All databases all tables all columns

 

-------------------------Part 1

--------------------------Part 1
 --------------------------Part 1
USE [master]
GO


create/alter PROC [dbo].[GlobalSearch]
AS
BEGIN
 
 
    IF OBJECT_ID('dbo.ListOfObj', 'U') IS NOT NULL
    DROP TABLE dbo.ListOfObj;
    CREATE TABLE master.dbo.ListOfObj (DatabaseNAME nvarchar(370), TableName nvarchar(3630), ColumnName nvarchar(3630))
	
    SET NOCOUNT ON
    DECLARE @DBname nvarchar(256),@TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET @DBname=''
    SET  @TableName = ''
   
    DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR    
	SELECT name FROM sys.databases  where database_id >6
    OPEN @getDBName
    FETCH NEXT
    FROM @getDBName INTO @DBName
    WHILE @@FETCH_STATUS = 0
    BEGIN
   ----------------------------------------
   ----------------------------------------
   
        declare @sql_v nvarchar(max)
        BEGIN
            SET @SQL_v = 'insert into master.dbo.ListOfObj select ''' + @DBName +''', t.name as table_name , c.name as column_name
                            from
                                ' + quotename(@DBName) +'.'+'sys.tables t '+
                                ' join ' + quotename(@DBName) +'.'+ 'sys.columns c on c.object_id = t.object_id  '+
                                ' join ' + quotename(@DBName) +'.'+ 'sys.types ty on ty.system_type_id = c.system_type_id'+
                            ' where type_desc = ''USER_TABLE''  
							and ty.name in (''text'',''varchar'',''char'',''nvarchar'',''nchar'')'
                            --modify below line to  search field datatype

            EXEC sp_executesql @SQL_v
            --PRINT @SQL_v
   
        END
   ----------------------------------------
   ----------------------------------------

   --print '-Blah-Blah-Blah' + @DBName
    FETCH NEXT FROM @getDBName INTO @DBName
    END
    CLOSE @getDBName
    DEALLOCATE @getDBName

    END
 GO




 --------------------------Part 2
 --------------------------Part 2
 --------------------------Part 2


create/alter PROC [dbo].[GlobalSearch_FinalCall]
(
    @SearchString nvarchar(100)
)
AS
BEGIN
   
    IF OBJECT_ID('dbo.FinalResultOutput', 'U') IS NOT NULL
    DROP TABLE dbo.FinalResultOutput;
    CREATE TABLE master.dbo.FinalResultOutput (DatabaseNAME nvarchar(370), 
	TableName nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
   
   

    exec dbo.GlobalSearch

	
    declare @SearchString2 nvarchar(100);
    SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''')
	

    DECLARE @DatabaseName nvarchar(2000), @TableName nvarchar(2000), 
	@ColumnName nvarchar(2000)
    DECLARE @ListOfObj CURSOR
    SET @ListOfObj = CURSOR FOR    
	SELECT DatabaseName, TableName, ColumnName FROM master.dbo.ListOfObj
    OPEN @ListOfObj
    FETCH NEXT
    FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
   ----------------------------------------
   ----------------------------------------
        
		declare @s nvarchar(max)
            BEGIN
              set  @s = (
                    'SELECT count(*) from ' + @DatabaseName +'..' + 
					@TableName + ' where ' + @ColumnName + ' like ''''%'+
					@SearchString+'%''' )
			 
  
               INSERT INTO FinalResultOutput
               EXEC
			   (
                    'SELECT distinct ''' + @DatabaseName +''',''' + @TableName + ''',''' 
					+ @ColumnName + ''',''' +@s +'''''
                    FROM [' + @DatabaseName+'].dbo.'+@TableName +
					'  WHERE ' + @ColumnName + ' LIKE ' + @SearchString2
                ) 
		

           
            END
   ----------------------------------------
   ----------------------------------------
    FETCH NEXT FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
    END
    CLOSE @ListOfObj
    DEALLOCATE @ListOfObj

    END
 GO
 
 exec [GlobalSearch_FinalCall] 'Test'
 select * from FinalResultOutput