Funny you should write an article about this. This is a good topic. I solved this problem in a similar but different way.

We have hundreds of SQL servers in our environment with thousands of changes going in a month. Over time objects become stale and no one really does the due diligence to clean up unused objects. I estimated that we had hundreds of gigabytes of unused tables as well as thousands of unused objects spread across hundreds of production databases.

We also have a mixture of SQL 2000, 2005 and 2008. So this technique had to work on all environments.

(Before reading keep in mind that what my process does is all configurable. You setup what servers and databases you want to monitor)

So I developed an automated process to collect object usage. I first take a snapshot of the target database and store the results in a table. This captures every object in the database and what objects use those objects (it goes through syscomments to find which objects are used by which objects). As someone else pointed out, what happens when a stored procedure calls a stored procedure, or a stored procedure uses a view...etc. This takes care of that concern.

Figuring out what objects use other objects is the easy part. Its figuring out what objects are used by ad-hoc queries that is hard. And for that we have to use a SQL trace.

The process starts a SQL trace to dump trace files out to directory for a given amount of time. Then I use an SSIS package to load trace files into a table and parse through all the SQL being executed against the server. I break apart every "word" in all the SQL and count each word as a potential object name. A stored procedure then executes after this that updates the meta data that was collected in the snapshot step. Keep in mind that this method has a short-coming and that is when you have a table named LA. If anywhere in your code there is a column called LA then its going to assume that the table LA is used. But I wasnt going to spend 6 months writing SQL parsing code. I think its "close enough"

When complete you will have a table that contains all the metadata you need to delete objects that are no longer used.

If you guys want the process let me know and I can email out the SSIS package and database scripts.

Below is the poorly formatted example of the results you will get:

ServerName DatabaseNameObjectNameObjectTypeUsedInProceduresUsedInFunctionsUsedInViewsUsedInTriggersUsedInAdHocQueries

NHQRPTSQL004ATT_WHSEsysconstraintsVNoNoNoNoYes

NHQRPTSQL004ATT_WHSEsyssegmentsVNoNoNoNoNo

NHQRPTSQL004AYS_AsurionCustomersUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionInventoryUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionInvoicesUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionLedgerInvoicesUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionPurchaseOrdersUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionRepairOrdersUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionRODetailsUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionShippingUNoNoNoNoYes

NHQRPTSQL004AYS_AsurionsyssegmentsVNoNoNoNoNo

NHQRPTSQL004AYS_AsurionsysconstraintsVNoNoNoNoYes

NHQRPTSQL004AYS_AsurionMSreplication_subscriptionsUNoNoNoNoYes

This is just the results of a view I wrote. There is enough metadata to drill into exactly what objects use each object 🙂