Using INFORMATION_SCHEMA views to quickly and easily find objects in SQL Server

By Steve Endow

Christina Phillips and I have done a SQL presentation at several Dynamics GP conferences, and one of the items I always like to mention is INFORMATION_SCHEMA.  It seems that many people don't know about this fantastic set of views, and are still suffering by using sysobjects.

I think that it is a very underutilized tool for Dynamics GP users and consultants.

INFORMATION_SCHEMA is a set of metadata views that allows you to quickly and easily find objects in SQL Server.  The best part about INFORMATION_SCHEMA is that the syntax is very simple and obvious.  It's vastly easier to use than sysobjects.

Here's an MSDN article that covers INFORMATION_SCHEMA views:

https://msdn.microsoft.com/en-us/library/ms186778.aspx


Let's jump straight into some examples.

What if you want to find every SOP table in the entire database:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'SOP%'


What if you want to find every SOPNUMBE field in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE'


What if you wanted to find every SOPNUMBE field in the SOP tables in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE' AND TABLE_NAME LIKE 'SOP%'


Inversely, what if you wanted to find every SOPNUMBE field that is NOT in a SOP table in the entire database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'SOPNUMBE' AND TABLE_NAME NOT LIKE 'SOP%' ORDER BY TABLE_NAME 



What if you wanted a list of all GP stored procedures related to the SOP1xxxx tables?

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'zDP_SOP1%'


What if you wanted to find any stored procedures that had been altered?

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE CREATED <> LAST_ALTERED 


One thing that I believe INFORMATION_SCHEMA cannot search for is triggers.  For triggers, you will still need to use sysobjects.  Here is a Stack Overflow post with some options for doing that.

I use INFORMATION_SCHEMA regularly to quickly track down tables and fields and find out which tables contain a given field.  It's a huge time saver and if you work with GP queries regularly, is something you should probably start using.