Author : Guest
Total Views : 146
Posted On : Fri, May 4, 2018
Updated On :

SQL SERVER – Table name exists in all procedures – Search in All Stored Procedure

In this article, we will see that how we can find the name of all stored procedures which contains a given table name in SQL Server.

Let's create a table first.

CREATE table Users
(
    Id INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(50) NOT NULL
)

Now let's create two stored procedures which are going to consume the above table in different ways.

--sp_Users is being used to add the record in Users table
CREATE PROC sp_Users
(
    @Name AS VARCHAR(50)
)
AS
BEGIN
    INSERT INTO Users VALUES(@Name)
END
 
--sp_Users_Select is being used to select the records from Users table
CREATE PROC sp_Users_Select
(
    @Name AS VARCHAR(50)
)
AS
BEGIN
    SELECT * FROM Users
END

Now the following query searching the table name in syscomments and sysobjects tables using INNER JOIN

SELECT DISTINCT SO.name,SO.xtype
FROM syscomments SC
INNER JOIN sysobjects SO ON SC.id=SO.id
WHERE sc.TEXT LIKE '%Users%'

OutPut