Click here to Skip to main content
15,036,145 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)
as 

		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'
		ELSE
		SET @SPName2 = 'data loaded in ' + CAST(@loaddate AS nvarchar(10))

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


----------------------------------------------------------------------------
------------------------------------[AdventureWorks2017].Person.viewPerson---------

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




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 tables.so 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[^]
Posted
Updated 14-Jul-20 6:43am
v2

1 solution

   

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