TSQL Query for Global Search in Database

Hello Friends,

          2 months back I was looking for a string saved in database from my application. Suddenly I found that the string value was not saved in expected SQL table. However the application-logs had no logs for any error raised by insert query.

          I was surprised and wondering where the hell my string value stored in database of approx. 15 GB of size and approx. 108 tables.

          Finally to help myself, I prepared a TSQL script that helps me to find a varchar-value among any table from a database. Later I extended the same script to support seeking multiple databases. 

          You just need to specify @Text_To_Search, @DBname and a flag - @Single_DB_Only to indicate whether to seek in single database OR all databases on the sql-server.

SET NOCOUNT OFF
GO

declare @Text_To_Search nvarchar(max)
declare @TableName nvarchar(max)
declare @ColName nvarchar(max)
declare @CMD nvarchar(max)
declare @DBname nvarchar(max)
declare @Single_DB_Only bit

 
set @Text_To_Search = 'SOMETHING'   -- Enter the Text here which you want to search
set @Single_DB_Only = 1     -- If @Single_DB_Only = 0 then the search will be in only one database
if @Single_DB_Only = 1
 set @DBname = 'MY_DATABASE'    -- Enter Database Name in which you want to search
 

/* ********************** Validation ********************** */
IF @Single_DB_Only = 1 AND NOT EXISTS(SELECT name FROM [master].[sys].[databases] WHERE name = @DBname)
BEGIN
 PRINT 'Please enter valid database name'
 RAISERROR('Please enter valid database name', 20, -1) WITH LOG
END
/*-- ********************** Validation ********************** */
 
if exists(select * from [master].[dbo].sysobjects where xtype = 'U' and [Name] = 'MY_INFO_ALL_TABLES')
  drop table [master].[dbo].MY_INFO_ALL_TABLES
create table [master].[dbo].MY_INFO_ALL_TABLES (TableName nvarchar(max), ColName nvarchar(max))
 
if exists(select * from [master].[dbo].sysobjects where xtype = 'U' and Name = 'MY_INFO')
  drop table [master].[dbo].MY_INFO
create table [master].[dbo].MY_INFO (DBName nvarchar(max), TableName nvarchar(max), ColName nvarchar(max), FieldValue nvarchar(max))

if @Single_DB_Only = 0
Begin
 declare CUR_DB cursor for select name from [master].[sys].[databases] where name not in('master','tempdb', 'model','msdb','tempdb') and state = '0' 
 open CUR_DB
 fetch next from CUR_DB  into @DBname
 while @@FETCH_STATUS = 0
 Begin
  print @DBNAME 
  
  set @CMD = 'insert into [master].[dbo].MY_INFO_ALL_TABLES (TableName, ColName) select T1.Name as TableName , T0.Name as ColName from [{3}].sys.syscolumns T0 inner join [{3}].sys.sysobjects T1 on T0.id = T1.id where T1.xtype = ''U'' and T0.Name not like ''MY_TABLES%'' '
  set @CMD = replace(@CMD,'{3}',@DBname)
  -- print @CMD
  exec (@CMD)
  
  declare CUR cursor for select TableName , ColName from [master].[dbo].MY_INFO_ALL_TABLES
  open CUR
  fetch next from CUR  into @TableName, @ColName
  while @@FETCH_STATUS = 0
  Begin
     set @CMD = 'insert into [master].[dbo].MY_INFO (DBName,TableName,ColName,FieldValue) select ''{3}'',''{1}'',''{0}'', cast({0} as nvarchar) from [{3}].dbo.{1} where {0} like ''%{2}%'' '
     set @CMD = replace(@CMD,'{0}',@ColName)
     set @CMD = replace(@CMD,'{1}',@TableName)
     set @CMD = replace(@CMD,'{2}',@Text_To_Search)
     set @CMD = replace(@CMD,'{3}',@DBname)
     -- print @CMD
     exec (@CMD)
     fetch next from CUR into @TableName, @ColName
  End
  close CUR
  deallocate CUR
  
  fetch next from CUR_DB  into @DBname
 End
 close CUR_DB
 deallocate CUR_DB
End
Else
Begin
 print @DBNAME 
 
 set @CMD = 'insert into [master].[dbo].MY_INFO_ALL_TABLES (TableName, ColName) select T1.Name as TableName , T0.Name as ColName from [{3}].sys.syscolumns T0 inner join [{3}].sys.sysobjects T1 on T0.id = T1.id where T1.xtype = ''U'' and T0.Name not like ''MY_TABLES%'' '
 set @CMD = replace(@CMD,'{3}',@DBname)
 -- print @CMD
 exec (@CMD)
 
 declare CUR cursor for select TableName , ColName from [master].[dbo].MY_INFO_ALL_TABLES
 open CUR
 fetch next from CUR  into @TableName, @ColName
 while @@FETCH_STATUS = 0
 Begin
    set @CMD = 'insert into [master].[dbo].MY_INFO (DBName,TableName,ColName,FieldValue) select ''{3}'',''{1}'',''{0}'', cast({0} as nvarchar) from [{3}].dbo.{1} where {0} like ''%{2}%'' '
    set @CMD = replace(@CMD,'{0}',@ColName)
    set @CMD = replace(@CMD,'{1}',@TableName)
    set @CMD = replace(@CMD,'{2}',@Text_To_Search)
    set @CMD = replace(@CMD,'{3}',@DBname)
    -- print @CMD
    exec (@CMD)
    fetch next from CUR into @TableName, @ColName
 End
 close CUR
 deallocate CUR
 
 fetch next from CUR_DB  into @DBname
End
 
select * from [master].[dbo].MY_INFO
GO

          Hope this TSQL script can save a lot time of yours,

          Enjoy!!!