Find List Of Tables Used In Stored Procedure Using Types Of Joins

This blog enables you to find the list of tables used in the stored procedure, using types of joins.

Introduction

Using SQL query, we can find out the list of the tables used in the stored procedure, using types of joins like inner join, outer join etc.

Description

Using SYSOBJECTS and SYSDEPENDS, we can get all the tables, stored procedure and other database object-related information. 

SELECT * FROM SYSOBJECTS 

select * from SYSDEPENDS

 

Steps

Create 3 tables named A , B and C.

create table A  
(  
  id int ,  
  address1 nvarchar(20)  
)  
  
create table B  
(  
  id int ,  
  address2 nvarchar(20)  
)  
  
create table C  
(  
  id int ,  
  address3 nvarchar(20)  
)

Create a procedure and mention 3 tables mentioned above, using inner joins.

Create procedure Sp_ListTables  
as   
begin  
set nocount on;  
select * from A   
inner join B on A.id = B.id  
inner join C on A.id = C.id  
end

Now, execute the procedure.

exec Sp_ListTables 

Using below mentioned important T-SQL query, we can get the list of the tables used in the stored procedure. 

SELECT   
NAME as 'List Of Tables'  
FROM SYSOBJECTS  
WHERE ID IN (   SELECT SD.DEPID   
                FROM SYSOBJECTS SO,   
                SYSDEPENDS SD  
                WHERE SO.NAME = 'Sp_ListTables'  ----name of stored procedures  
                AND SD.ID = SO.ID  
            )

Here, I put two objects SYSOBJECTS, SYSDEPENDS and put some inner join on the columns of these two objects.

SELECT SD.DEPID   
                FROM SYSOBJECTS SO,   
                SYSDEPENDS SD  
                WHERE SO.NAME = 'Sp_ListTables'  ----name of stored procedures  
                AND SD.ID = SO.ID

Summary

We learned about SYSOBJECTS and SYSDEPENDS in addition to learning the stored procedure, which is used in SQL query to get the table lists.