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
