Finding table name based on a specific value

Raghavender Chavva, 2018-04-17 (first published: 2018-04-13)

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