Find Store Procedure in SQL Server by Table Name

This SQL Server tutorial will teach how to find the store procedure by table name in SQL Server.

There are 3 ways to find store procedure names in SQL Server by table name. Here are the methods which we are going to use:

1. Using sys.procedure table
2. Using INNER JOIN clause
3. Using table dependencies

Find Store Procedure in SQL Server by Table Name using sys.procedure table

The SQL Server SYS.PROCEDURES is a system table that carries the procedure name, object Id and other details like created date, modified date, etc. If we want to know more about the store procedure, we can use this option to find the list of procedures having the phrase in the name.

EXAMPLE:

SELECT Name as TABLE_NAME
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%HumanResources%'

With the help of the SELECT statement, we retrieve the table name of the store procedure from the sys.procedures as a system table by the WHERE condition.

In the WHERE condition, we use object definition on the store procedure’s object ID which checks the table name like “HumanResources”.

PROCEDURES_NAME

uspGetEmployeeManagers

uspGetManagerEmployees

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

uspSearchCandidateResumes

This will help to find store procedures by table name in SQL Server. We hope you understand how to find the store procedures in SQL Server by using the SYSTEM TABLES in the SELECT statement.

Read: SQL Server stored procedure insert into

Find Store Procedure in SQL Server by Table Name using INNER JOIN clause

Here we will see how to find the store procedure name by using the SQL Server INNER JOIN and TABLE NAME in SQL Server.

EXAMPLE:

SELECT DISTINCT o.name AS PROCEDURE_NAMES
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%HumanResources%' 
AND o.type_desc !='VIEW';

With the help of INNER JOIN in the SELECT statement, we retrieve a unique store procedure name from the system tables: SYS.SQL_MODULES and SYS.OBJECTS by the WHERE condition.

In the WHERE condition, we define the table name that starts within the HUMANRESOURCES. And also check the system’s sequential modules which are not equal to VIEW as the view of the table name.

PROCEDURES_NAME

dEmployee

ufnGetContactInformation

uspGetEmployeeManagers

uspGetManagerEmployees

uspSearchCandidateResumes

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

This will bring the store procedure names based on the table name to the resultset. You should now understand how to use the SQL Server INNER JOIN function with the system tables to fetch the store procedure name from the table name.

Read: How to view stored procedure in SQL Server

Find Store Procedure in SQL Server by Table Name using table dependencies

The SQL Server SP_DEPENDS function is used to display information about the database object dependencies such as views or procedure that depends on the table. Its references to objects outside the current database are not reported. ,

NOTE

This feature will be removed from the future version of Microsoft SQL Server. Avoid using this feature, instead use the sys.dm_sql_referencing_entities and sys.dm_sql_references_entites instead.

Let’s see the syntax of the sp_depends function.

sp_depends [ @objname = ] '<object>'   
  
<object> ::=  
{  
    [ database_name. [ schema_name ] . | schema_name.  
    object_name  
}

In the syntax explanation:

• database_name: It is the name of the database.
• schema_name: It is the name of the schema to which the object belongs.
• object_name: It is the database object to examine the dependencies. The object can be a table, view, store procedure or user-defined function or trigger. And the object_name is nvarchar(766) with no default.

Here we will use the sp_depends method to find the store procedure name from the table name.

EXAMPLE:

EXEC sp_depends @objname = N'HumanResources.Department';

With the help of the EXEC statement, we used the SP_DEPENDS function on the object name which is equal to HumanResources.Department. So, this will bring all store procedure that depends on the table or view in the resultset.

NAME	TYPE
HumanResources.vEmployeeDepartment	View
HumanResources.vEmployeeDepartmentHistory	View

We hope you understand how to use the SQL Server sp_depends function to find the store procedure in SQL Server by table name.

You may also like to read the following SQL Server tutorials.

• SQL Server stored procedure modified date
• SQL Server stored procedure parameters
• SQL Server stored procedure return value

Conclusion

We hope you understand how to get the store procedure in SQL Server using table names. And we have explained in depth the different subtopics.

• Using sys.procedure table
• Using INNER JOIN clause
• Using table dependencies