How to find tables used in stored procedure in sql server

In this "How to find tables used in stored procedure in sql server"article i am going to discuss about some SQL queries which are useful in our real time projects. We might have done these things manually but now lets see how useful these queries turns out to be.

First SQL query what i wanted to discuss is -



How to find all stored procedure names which uses the given table

Query -

SELECT DISTINCT 
 obj.Name
 ,com.Text
FROM sysobjects obj 
     INNER JOIN syscomments com ON obj.ID = com.ID
WHERE obj.Type = 'P' AND com.Text LIKE '%' + 'yourtablename' + '%'
ORDER BY obj.Name

Lets create a function use this query in function -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllStoredProcsByTableName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllStoredProcsByTableName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all stored procedure details which is using given table
-- =============================================
CREATE FUNCTION FN_GetAllStoredProcsByTableName 
( 
 @tableName VARCHAR(50)
)
RETURNS 
@SPDetails TABLE 
(
 Name VARCHAR(50)
 ,Text VARCHAR(5000)
) 
AS
BEGIN
 
 INSERT INTO @SPDetails
 (
  Name,
  Text
 )
 SELECT DISTINCT 
  obj.Name
  ,com.Text
 FROM sysobjects obj 
  INNER JOIN syscomments com on obj.ID = com.ID
        WHERE obj.Type = 'P' AND com.Text LIKE '%' + @tableName + '%'
        ORDER BY obj.Name
 
 RETURN;
END

As you can see in the above function, table name is passed as an parameter and this table name is searched in all the stored procedures (Type = 'P'). Now lets execute the above function and lets see how the results will look like.

Before executing this function i have created tables "Student" and "Class"(Here i am not giving the table structures as these are trivial)and created a stored procedures "SP_GetAllStudents" and "SP_GetAllClasses" for demonstration purpose, which returns all the students and class details respectively nothing fancy.
Stored procedure SP_GetAllStudents -

CREATE PROCEDURE [dbo].[SP_GetAllStudents]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    SELECT 
 Classes.ClassID 
 ,Students.StudentID
 ,Students.StudentName
    FROM Classes
     INNER JOIN Students ON Classes.ClassID = Students.ClassID
END

Now lets execute the function -

SELECT * FROM dbo.FN_GetAllStoredProcsByTableName('students')

Above function returns below resultset -

Second stored procedure is not filtered because in that stored procedure only "Class" table is used.
Now lets move onto the next SQL query -

How to find tables used in stored procedure in sql server

Query -

SELECT DISTINCT 
  obj2.Name
FROM sys.sysobjects obj
 INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
 INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
WHERE obj.Type = 'P' and obj.Name = 'SP_GetAllStudents'

Now lets create a new function as we created earlier -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllTableNamesBySpName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllTableNamesBySpName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all tables used in given SP name
-- =============================================
CREATE FUNCTION FN_GetAllTableNamesBySpName 
( 
 @SPName VARCHAR(50)
)
RETURNS 
@TableNames TABLE 
(
 Name VARCHAR(50)
) 
AS
BEGIN
 
 INSERT INTO @TableNames
 (
  Name
 )
 SELECT DISTINCT 
  obj2.Name
 FROM sys.sysobjects obj
  INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
  INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
 WHERE obj.Type = 'P' and obj.Name = @SPName
 
 RETURN;
END

As you can see above stored procedure name is passed as a parameter and table names are searched. This will give below result set once executed with
"SP_GetAllStudents" -

SELECT * FROM dbo.FN_GetAllTableNamesBySpName('SP_GetAllStudents')

Hope this article is useful. Please comment your thoughts.