It is very common to see in a relational database that stored procedures are using many Tables, Views, Sequence Object, Stored Procedures, Functions, or many more to process the business logic. If you happen to work on a Stored Procedure performance issue which referenced many objects, you might end up asking yourself – “Is the SP itself running slow? or it is poorly performing because of the other referenced objects like functions, stored procedure, or many more.”

In this tip, I am going to share a useful script. You can use the script to check Stored Procedures have referenced which all the objects.

Determine Referenced Objects in the Stored Procedures

use <databasename>
go
SELECT DISTINCT
        object_schema_name(Referencing_ID) as SchemaName,
object_name(Referencing_ID) as SPName, 
referencer.type,  
referenced_database_name,
referenced_schema_name,
referenced_entity_name,
        referenced.type
FROM sys.sql_expression_dependencies
LEFT JOIN sys.objects referencer ON referencing_id = referencer.object_ID
LEFT JOIN sys.objects referenced ON referenced_id = referenced.object_ID
WHERE   referencing_Class = 1 AND referenced_class = 1 AND referencer.type IN ('p')
--AND object_name(Referencing_ID) = ''

For example: The above script has been executed against “WideWorldImporters” database to check the SP “InsertCustomerOrders” has referenced how many tables, functions and SO.

To get the complete name of the Object Type, you can refer the below list;

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
U = Table (user-defined)
V = View
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
UQ = UNIQUE constraint
X = Extended stored procedure

I hope you find it helpful during figuring out referenced objects in Store Procedures.

The post Determine Referenced Objects in the Stored Procedures appeared first on .