Search for string in the entire database

This is the query which searches for string in the entire DB.
Provide 2 inputs as DB name and Search string.
This query takes more time, but will sure get you the results.
You can customize the search criteria…
———————————————————-
Use [Database_Name]
go
declare @SearchChar varchar(8000)
Set @SearchChar = ‘Search_Text’ — Like ‘yuva%’, ’11/11/2006′
declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)
declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM
sysobjects b
WHERE
b.type=’u’ and b.Name <> ‘dtproperties’
order by b.name
open dbTable
fetch next from dbTable into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT
c.Name as ColumnName
FROM
sysobjects b,
syscolumns c
WHERE
C.id = b.id and
b.type=’u’ and b.Name = @TableName
order by b.name
open db
fetch next from db into @ColumnName
set @CMDMain = ‘SELECT ‘ + char(39) + @TableName + char(39) + ‘ as TableName,’+
‘ [‘+ @TableName + ‘].* FROM [‘ + @TableName + ‘]’+
‘ WHERE ‘
set @CMDMainCount = ‘SELECT Count(*) FROM [‘ + @TableName + ‘] Where ‘
Set @CMDJoin = ”
WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + ‘Convert(varchar(5000),[‘ +@ColumnName + ‘]) like ‘ + char(39) + @SearchChar + char(39) + ‘ OR ‘
fetch next from db into @ColumnName
end
close db
deallocate db
Set @CMDMainCount = ‘If (‘+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) – 3)+ ‘) > 0 Begin ‘
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) – 3)
Set @CMDMain = @CMDMain + ‘ End ‘
Print @CMDMain
exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable

———————————————-

-Yuva