All geek questions in one place

How to get the name of the entire table (s) that is used in a particular stored procedure?

Do I have a requirement to get all database table names that are used in a particular stored procedure?

As an example , I have one stored procedure as shown below.

CREATE PROCEDURE [dbo].[my_sp_Name] @ID INT = NULL AS BEGIN SELECT ID, NAME, PRICE FROM tbl1 INNER JOIN tbl2 ON tbl1.ProductId = tbl2.ProductId LEFT JOIN tbl3 ON tbl2.ProductSalesDate = tbl3.ProductSalesDate LEFT JOIN tbl4 ON tbl1.ProductCode = tbl4.ItemCode END

Expected Result:

Used_Table_Name

  • tbl1
  • tbl2
  • tbl3
  • tbl4

Can anyone suggest a way?

+5
source share
2 answers

Use below script to get table names in your store procedure:

  SELECT DISTINCT [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , o.type_desc FROM sys.dm_sql_referenced_entities ('[dbo].[Your_procedurename]', 'OBJECT')d JOIN sys.objects o ON d.referenced_id = o.[object_id] WHERE o.[type] IN ('U', 'V')
+2
source

The query below will help you use database tables in a stored procedure.

 ;WITH stored_procedures AS ( SELECT oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P' AND o.name = 'my_sp_Name' ) SELECT Table_name AS 'Used_Table_Name' FROM stored_procedures WHERE row = 1
+3
source

Source: https://habr.com/ru/post/1266251/

More articles:


All Articles