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.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com