In this blog, I have explained the following concepts:
- How to get a list of tables used in Procedure.
- How to get a list of Procedures, Functions and Views used by table.
Run the following script in your database, it will create the USP_GetTableByProcedure procedure. The procedure accepts two optional parameters: @ProceureName and @TableName. If you provide procedure name, it lists all the tables which are used in that procedure. If you provide table name, then you will get all the procedures which are used in the table.
- Create procedure USP_GetTableByProcedure
- @ProceureName varchar(50)=Null,
- @TableName varchar(50)=Null
- AS
- BEGIN
- SELECT DISTINCT O.NAME AS 'Procedure_Name' , OO.NAME AS 'Table_Name'
- FROM SYSDEPENDS D, SYSOBJECTS O, SYSOBJECTS OO
- WHERE O.ID=D.ID
- AND O.NAME= ISNULL(@ProceureName,O.NAME)
- AND OO.ID=D.DEPID
- AND OO.NAME = ISNULL(@TableName,OO.NAME) -- Table PROCEDURE NAME
- ORDER BY O.NAME,OO.NAME
- END
Testing : Test the above stored procedure in the following two ways:
- Execute the Stored Procedure by passing stored procedure name to get the tables that are used in.
- Execute the Stored Procedure by passing table name to get the procedure that is used by.
According to my database I use Mas_Table table and USP_Get_AllTeams procedures as parameters -
Step 1: Exec USP_GetTableByProcedure 'USP_Get_AllTeams' , Null.
In the above output as you can see the list of tables that are used in USP_Get_AllTeams procedure.
Step 2: Exec USP_GetTableByProcedure Null , 'Mas_Team'.
In the above output as you can see the list of procedures that are used with Mas_Table.
I hope you enjoyed it.