Friday, December 17, 2010

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...

No comments:

Post a Comment

Text Editor Tips: Some Useful Brackets Extension

Brackets  is an open source and free-to-use text editor and primarily being used by web developers and designer. I mainly use Brackets for d...