Click here to Skip to main content
15,665,276 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
Hi Team

i am trying to get a query for below ask

Find all objects (like View, table, functions ,parameters and variable ,variable values ) in Stored procedure

but finding the query ,seems its not possible .anyone could you please help me

Script :
create   procedure dbo.Sp_Person_var (@loaddate int)

		DECLARE @SPName NVARCHAR(200)='using Sp',
		    @Source NVARCHAR(200) = 'loading from view',
	        @Target NVARCHAR(200) = 'to table',
			 @SPName2  NVARCHAR(200)

		IF(@loaddate IS NULL)
		SET @SPName2 = 'full load'
		SET @SPName2 = 'data loaded in ' + CAST(@loaddate AS nvarchar(10))

insert into [AdventureWorks2017].[Person].[Person2]  ([BusinessEntityID], [PersonType]) 
SELECT  [BusinessEntityID]       ,[PersonType]   FROM [AdventureWorks2017].Person.viewPerson 


create view [Person].[viewPerson] 
SELECT  [BusinessEntityID]  ,[PersonType]   FROM [AdventureWorks2017].[Person].[Person]

here i need to get out put like below

'Name' 'values' 'type' 'SPname' 'Data taken' 'datataken source'
'SPName' 'using Sp' 'V' 'dbo.Sp_Person_var' 'Person.viewPerson' 'Person.Person'
Source loading from view V
Source to table V
SPName2 'Full or loaddate
will get from
last run' V

Note : every run we will save the @SPName2 values in a 'RESULT' separate we need to take @Spname2 value from 'RESULT' table

Note : some times SPname ,Data taken ,datataken source column values text some time completely different ..

Could you please help me with a sql query .

Thanks in Advance

What I have tried:

Get list of tables used in a stored procedure – SQLServerCentral[^]
Updated 14-Jul-20 6:43am

1 solution

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900