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