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.