Find List Of Tables Used In 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.