Agent Jobs

Run A Quick Wild Card Search Across All Tables And Columns

Here’s some quick sql logic to perform a wild card search across all tables and columns. In this example I’m looking for any tables or views that contain a column with the name of ‘user’. Why am I doing this? So that I can drill down to see what kind of user information is stored in the application. This is essential when doing migrations so you can ensure that all the appropriate users are migrated. Additionally; it’s a great way to perform application auditing for the apps teams.

 use mydb;
set nocount on

,	isc.table_name
,	isc.column_name
information_schema.columns isc join information_schema.tables ist on isc.table_name = ist.table_name where
column_name like '%user%'
order by
,	column_name asc

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s