SQL Tips : SQL Server System Views - Searching tables that contain several columns name

Have you ever searching for tables that contain several column names? That would be a piece of cake job if you just have to look in small database with less than 100 tables. What if that database is in a gigantic size with about thousands of tables? I guess it'll be your nightmare jobs for you, don't you?

But, don't be afraid, I had had the same experience with you back then.

This morning, I had to find all tables that contain company code and location code within more than 2 thousands of tables in the SQL Server database system. Wow... that will be a pretty tough job if you don't know the trick...

Here is the secret: Have you ever heard SYSTEM VIEWS?

Yeah, SYSTEM VIEWS will give a huge help for you. System Views contain all object that reside within the database, such list of tables, columns, primary key, etc.

For example: you can get the list of tables inside your database by calling this statement :

SELECT * FROM SYS.TABLES
pretty neat don't you think?

and now, here the way I finish my job less than 1 minute:

SELECT * FROM SYS.TABLES

WHERE OBJECT_ID IN (SELECT A.OBJECT_ID FROM 

(SELECT * FROM SYS.COLUMNS

 WHERE NAME = 'COMPANY_CODE') A,

(SELECT * FROM SYS.COLUMNS

WHERE NAME = 'LOCATION_CODE') B

WHERE A.OBJECT_ID = B.OBJECT_ID)

Happy searching then...