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

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s