How To Find the Objects and Columns a Stored Procedure or View Uses

By Lori Brown | Dependencies

Jul 19

In my last post I covered finding objects that reference another object using the dmf, sys.dm_sql_referencing_entities.  (https://www.sqlrx.com/finding_things_sql_that_reference_a_table/ )  Now let’s reverse that and find what objects are referenced in stored procedures or views.  When updates are needed to business logic often that happens in these objects.  If the stored procedures or views are large with thousands of lines of code, figuring out everything that is used or referenced by these objects can be a nightmare.  And as in our previous case, if you are making changes to tables you need to know if your stored procedures or views actually use the table or even the specific column in the table that you are changing.

Using the dmv, sys.dm_sql_referenced_entities can help us out in this case.  (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referenced-entities-transact-sql?view=sql-server-2017)

I picked out a stored procedure and view in the WideWorldImporters database that use several different objects to see how this works.

USE WideWorldImporters
GO
SELECT referenced_entity_name AS table_name, referenced_minor_name as column_name, is_selected, is_updated, is_select_all  
FROM sys.dm_sql_referenced_entities ('Website.InvoiceCustomerOrders', 'OBJECT');  -- stored procedure

SELECT referenced_entity_name AS table_name, referenced_minor_name as column_name, is_selected, is_updated, is_select_all  
FROM sys.dm_sql_referenced_entities ('Website.Suppliers', 'OBJECT');  -- view
GO

Here are the results for the Website.InvoiceCustomerOrders stored procedure.  The list returned was pretty long but you can see what tables and columns are used in the proc and you see if it is updated or simply listed in a select statement.

And here are the results for the Website.Suppliers view.  The list returned was pretty long again but you can see what tables and columns are used and that all are simply selected as you would expect in a view.

The is_select_all column will only be populated if there is a “SELECT * FROM …“ being used in the proc or view.

I will caution you to also search a little further using sys.sql_modules, searching in job commands and SSIS packages for the things you are changing.  In the stored procedures, if you use dynamic SQL strings and reference tables there, sys.dm_sql_referenced_entities will not find references to the objects.  In my last post the stored procedures [Application].[Configuration_EnableInMemory] & [DataLoadSimulation].[Configuration_ApplyDataLoadSimulationProcedures] do use the Sales.Orders table but in dynamic SQL strings.  The results of sys.dm_sql_referenced_entities does not pick this up at all.

In my examples I have used the Wide World Importers database.  This sample database replaces the old AdventureWorks databases.  You can download the Wide World Importers OLTP and Data Warehouse databases here:

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Find out more about them here: https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-what-is?view=sql-server-2017

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!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>