How to find all tables and columns used in the Stored Procedures?

As a part of modulerizing business logic, we create stored procedures and functions with SQL Server databases and in most cases, stored procedures are used. A stored procedure is named collection of TSQL statements that is created for implementing a business logic. 

When implementing a stored procedure, you will use one or more tables within it and later you may need to find out all tables along with columns that have been used with your specific stored procedures. Yes, you can open the stored procedure and see the code for finding them but what if you need to do it programmitically and not only one, for few stored procedures? Or what if you need to check and see a particular table has been used with your specific stored procedures? How can we find out these info, or in other words, how can we find out dependencies?

There are few ways of finding them using system views and functions given. We can use  sys.sql_expression_dependencies  view for finding objects used with a user-defined object and it can be used with stored procedures. However, since it returns one-row-per-one-object, you will not be able to get the column list of a table if you use this with a stored procedure. The dynamic management function that is sys.dm_sql_referenced_entities can be used as well. This gives more details on referenced objects. For an example, if you search for a stored procedure, it returns all tables along with column names used with the stored procedure.

Following code shows how to get the tables and columns used in dbo.uspGetBillOfMaterials stored procedure. As you see, sys.sql_expression_dependencies does not provide column details but sys.dm_sql_referenced_entities dynamic management function provides all the details we need.

USE AdventureWorks2014;
GO

SELECT * FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.uspGetBillOfMaterials');  

SELECT * 
FROM sys.dm_sql_referenced_entities(N'dbo.uspGetBillOfMaterials', N'OBJECT');

In case of you need to find out whether the object is being used other objects, we have another dynamic management function named sys.dm_sql_referencing_entities. This returns details of all referencing objects.

SELECT * 
FROM sys.dm_sql_referencing_entities(N'Production.Product', N'OBJECT');

In case of you need to know how your object has been used by others, here is the way. This shows how to use sys.dm_sql_referencing_entities dynamic management function to see how the Production.Product table is used by other objects.