sql query to list all procedures,views ,temp tables from a specific stored procedure

Hi @Farhan Jamil ,

As mentioned in sys.dm_sql_referenced_entities, dependency information is NOT created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

Can i have an assistance of a similar like one combined query which can be used to get all database, views(what table and column is being referenced), temp tables(what table and column is being referenced), functions

You could refer below query which could find all tables together with their referred columns, views, procedures and functions in whole database and check whether it is helpful to you.

SELECT  DB_NAME() AS DatabaseName,   
 d1.referenced_schema_name SchemaName,   
 d1.referenced_entity_name TableName,   
 c.name columnname,  
 o1.type,  
 case when o1.type ='P' then 'StoredProcedure'   
      when o1.type ='TF' then 'Function'   
      when o1.type ='V' then  'View' end Type ,   
 OBJECT_NAME(d1.referencing_id) Name  
 FROM sys.sql_expression_dependencies  d1   
 JOIN sys.objects o   
 ON  d1.referenced_id = o.[object_id]  
 JOIN sys.objects p   
 on OBJECT_NAME(d1.referencing_id)=p.name    
 join sys.objects o1   
 on OBJECT_NAME(d1.referencing_id)=o1.name    
JOIN sys.columns c ON d1.referenced_id = c.[object_id]   
 where  o.type_desc ='USER_TABLE'  
 and o1.type in ('P','TF','V')  
 order by DB_NAME() ,referenced_schema_name,referenced_entity_name  

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Dependency information is not maintained for temporary objects and it would be difficult to do so since a temp object can have different schema/code depending on the context.

I don't know of a quick an easy way but what I've done in the past for object-level dependencies is load tables with dependency info for all objects in every database and server in the environment such that every referencing/referenced object has a 4-part name. This requires transformations to normalize server names, populate missing server, database, schema columns.

It's not trivial but does provide valuable information for reference and change impact analysis. There may be third-party tools to facilitate this.