Finding the table name and column name where a specific string has been used

Finding the table name and column name where a specific string has been used:

----- Updated One ----

Set NOCOUNT ON
Declare @count bigint
declare @setvalue bigint
declare @countvalue varchar(max)
DECLARE @TProduct TABLE
(
count bigint identity (1,1),
query varchar(max)
) 
--Insert data to Table variable @Product 
INSERT INTO @TProduct(query)
select 'select @countvalue=count (['+COLUMN_NAME+']) from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''%@%.com''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
set @setvalue=1
set @count=(select count(*) from @TProduct)
--select * from @TProduct
--print @count
while (@setvalue <=@count )
begin
Declare @query nvarchar(500)
set @query=(SELECT query FROM @TProduct where count=@setvalue)
--print @query
exec sp_executesql @query, N'@countvalue int out', @countvalue out
--print @countvalue
--declare @queryres table (Queryres bigint)
--insert @queryres exec (@query)
--select * from @queryres
if(@countvalue > 0)
print @query
--delete from @queryres
set @setvalue=@setvalue+1

end

----- Updated One ----

Set NOCOUNT ON
Declare @count bigint
declare @setvalue bigint
DECLARE @TProduct TABLE
(
count bigint identity (1,1),
query varchar(max)
) 
--Insert data to Table variable @Product 
INSERT INTO @TProduct(query)
select 'select count ('+COLUMN_NAME+') from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''%@%.com''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
set @setvalue=1
set @count=(select count(*) from @TProduct)
--select * from @TProduct
--print @count
while (@setvalue <=@count )
begin
Declare @query varchar(max)
set @query=(SELECT query FROM @TProduct where count=@setvalue)
--print @query
declare @queryres table (Queryres bigint)
insert @queryres exec (@query)
--select * from @queryres
if((select queryres from @queryres) > 0)
print @query
delete from @queryres
set @setvalue=@setvalue+1

end



----- Updated One ----
Below script checks the count of the value we passed in all tables and all columns. If count is greater than zero it will display the select query on that column and table.

Set NOCOUNT ON
DECLARE @TProduct TABLE
(
query varchar(max)
)
--Insert data to Table variable @Product
INSERT INTO @TProduct(query)
select 'select count ('+COLUMN_NAME+') from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''String value''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
Declare @query varchar(max)
declare @queryres table (Queryres bigint)
DECLARE db_cursor CURSOR FOR
SELECT query
FROM @TProduct
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
--print (@query)
--exec (@query)
insert @queryres exec (@query)
--select * from @queryres
if((select queryres from @queryres) > 0)
print @query
Delete from @queryres
FETCH NEXT FROM db_cursor INTO @query
END
CLOSE db_cursor
DEALLOCATE db_cursor


go


---- Previous Script ------

DECLARE @TProduct TABLE
(
query varchar(max)
)
--Insert data to Table variable @Product
INSERT INTO @TProduct(query)
select 'select ['+COLUMN_NAME+'] from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''string value''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
Declare @query varchar(max)
declare @queryres bigint
DECLARE db_cursor CURSOR FOR
SELECT query
FROM @TProduct
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
print (@query)
exec (@query)
FETCH NEXT FROM db_cursor INTO @query
END
CLOSE db_cursor
DEALLOCATE db_cursor


go