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!!!
