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
Finding Store Procedure in SQL Server by Table Name

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
Example of Find Store Procedure in SQL Server by Table Name by using Sys.procedure table

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
Example of Find Store Procedure in SQL Server by Table Name by using INNER JOIN clause

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.

NAMETYPE
HumanResources.vEmployeeDepartmentView
HumanResources.vEmployeeDepartmentHistoryView
Example of Find Store Procedure in SQL Server by Table Name by using table dependencies

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.

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