sp_get_basetable_list

At work, we make extensive use of views & synonyms. The reasons are complex (and fodder for another post), but here’s a simplified example:

Pricing data is updated multiple times per day, and we want to keep multiple generations of the pricing table online. By pointing the synonym to the latest pricing table, we can redirect users to the current/correct data. If there’s a problem, we can “roll back” the latest pricing updates by simply pointing the synonym back to the prior generation of pricing data.

Obviously, this can create some confusion when you do need to find the actual table (eg, to see indexes, or to update data). What pricing table is the real pricing table?
Sure– you can query system views to find out the real table(s) referenced by the synonym or view.
When you do this hundreds (or thousands) of times, it can really add up. Time to automate.

First, let’s spec out what we’re going to automate:

• Create a stored procedure that accepts an object name, and returns a result set of tables
  • It would be cool if we could pass in multiple objects and handle them all at once
• If I pass in a table name, return the table itself
• If I pass in a synonym name, return the base table of that synonym
• If I pass in a view, return ALL the base tables of that view
• If I pass in gibberish (ie, not an object), return an empty result set
• If I pass a stored procedure, return all the base tables that get referenced by that stored procedure.
• Work recursively when you have a view of views (or similar)
• Work for any object in any database on the server

Seems like a daunting list, but it actually turned out to be pretty simple. Let’s look at how we’ll accomplish all those things using SQL:

• Work for any object in any database on the server
  • When we get an object name in the input parameter, expect it could be in the form Database.Schema.Object. PARSENAME() will help us make sense of that
  • If we put this in master & mark it as system, we could get more flexibility in how we use it.
  • Some dynamic SQL inside the stored procedure will be needed  to look into system views in other databases
• Work recursively when you have a view of views (or similar)
  • Easy enough. Whatever we do, we’ll do it in a WHILE loop until we’re left with only tables
• Finding the base tables given different object types
  • A quick peek into sys.dm_sql_referenced_entities should take care of this for us, regardless of what type of object we pass
• Create a stored procedure that accepts an object name, and returns a result set of tables
  • We’ll have an @object_name parameter
  • We’ll use #tables as a temp table as a work table to build our results, and output that table when we’re done.
• It would be cool if we could pass in multiple objects and handle them all at once
  • If I create & populate #tables before I call my stored procedure, then I can use that instead of the @object_name parameter.

I was able to turn this into a stored procedure using less than 100 lines of code. Take a look at the code, and give it a shot for yourself. You can download it here.

This stored procedure has become the basis for a few other procedures I’ve created. Some of those are incredibly useful for me, but this is the cornerstone of that other code.