SQL Query to find tables by name and column

This script will find tables where the table name is like a value and it contains a column name that is like some value

SELECT TABLE_NAME as TableName , c.Name as ColumnName
FROM INFORMATION_SCHEMA.TABLES ist 
join sys.tables st on 
ist.TABLE_NAME = st.name 
join sys.columns c 
on c.object_id = st.object_id 
WHERE (1=1)  
and ist.TABLE_TYPE = 'BASE TABLE' 
and  ist.Table_Name like '%account%' 
and c.name like '%agent%'

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