Finding table name based on a specific value

,

Some months ago when I was working on upgrade project I got a request from application team to provide the table and column names which are containing email ids.

This request is because, we have copied one of the production database to SIT environment and application’s one of the functionalities is to send emails users by fetching the emails ids from the tables.

As we are working in SIT environment there might be chance of sending false alerts to users. So we need to update those tables with our team members mail ids.

But the challenge was we have thousands of tables and not sure what are all the tables has the email ids and from column names also we didn't get the hint. Manually checking each table is like very huge time taking.

So I prepared below script which fetches the tables name and column name from INFORMATION_SCHEMA.COLUMNS and  sysobjects where the email ids are stored.

Person should have DB Owner previlages on the database and Run this script on the specific database only.

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

Rate

2.2 (5)

Share

Share

Rate

2.2 (5)