Total Views : 146
Updated On :
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%'