Using sp_help To Find Object Dependencies

I have been working on some things lately that have required that I find objects with specific data types and collations and then find those objects dependent objects and anything else that uses those objects. My end goal was to ultimately create drop and create statements for the things I found since we were changing the database collation. It’s not as easy as you might think it is because you can go down a rabbit hole of discovery on databases that have lots of tables, views, functions and stored procedures.

This has inspired me to start a series on things that can help you do that. To me the first place to start looking for information on an object and its dependencies can be found by using sp_help (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-2017). sp_help is a stored procedure that will return one or more result sets based on the object that you tell it to look at.

Here is sp_help returning information about the Fact.Transaction table in the WideWorldImportersDW database. Please note that you have to use schema.objectname to get results to return. If you don’t you will receive an error message saying that the object does not exist.

USE WideWorldImportersDW
GO
EXEC sp_help 'Fact.Transaction'

You can see that it does give a lot of information that is not easily seen in SSMS without opening a lot of things.

You can get a list of all objects in the database by running sp_help with no object specified, but the list also returns system objects and user defined data types but is missing the schema information. So, be careful to get the correct schema elsewhere. Little things like this can drive you crazy until you figure it out.

The output of most things you can view with sp_help is often multiple result sets so you can’t put the results into a table very well if at all. But, you can easily view the code that runs sp_help and do a little reverse engineering and develop something that would take each dependent result and put it into a table for later work such as creating DROP or CREATE statements.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!