Find all objects (like view, table, functions , parameters and variable , variable values ) in stored procedure

See more:

SQL

SqlDataSource

, +

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[^]