Find stored procedure in sql server by table name

This SQL Server tutorial will teach how to find stored procedure in SQL Server using table name.

How to find stored procedure in SQL Server using table name

To find stored procedure in SQL Server using table name, we can use the below three simple approaches.

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

Approach-1: Using sys.procedure table

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

EXAMPLE:

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

Using the SELECT statement, we retrieve the store procedure’s table name from the sys.procedures as a system table by the WHERE condition.

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

PROCEDURES_NAME

uspGetEmployeeManagers

uspGetManagerEmployees

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

uspSearchCandidateResumes

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

Approach-2: Using INNER JOIN clause

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

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 check the system’s sequential modules that 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 stored procedure name from the table name.

Read: How to view stored procedure in SQL Server

Approach-3: Using table dependencies

The SQL Server SP_DEPENDS function displays information about the database object dependencies, such as views or procedures that depend 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: The name of the schema to which the object belongs.
• object_name: This is the database object to examine the dependencies. The object can be a table, view, store procedure, or user-defined function or trigger. The object_name is varchar (766) with no default.

Here we will use the sp_depends method to find the stored 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 procedures that depend on the table or view to 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. We have explained the different subtopics in depth.

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