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

Question

Hi Guys

Need a bit of assistance here.
We have Stored proc designed in our live system which queries views, function, linked server((which queries tables and its columns)) etc

Is their a quick and easy way of getting list of all views(so what tables and columns is it referencing to) , temp tables(so what tables and column is it referencing to),tables (what column is it referencing too from a specific SP.

So for example

So far I have been using this

SELECT referenced_database_name,
case when referenced_Schema_name = '' or referenced_schema_name is null then 'dbo' else referenced_schema_name end as [referenced_schema_name],
referenced_entity_name,
referenced_minor_name,
(referenced_entity_name+'.'+referenced_minor_name)as Field,
((case when referenced_Schema_name = '' or referenced_schema_name is null then 'dbo' else referenced_schema_name end )+'.'+referenced_entity_name) as [Table],
case when referenced_database_name in ('Catalogue','Reflex') then referenced_database_name else NULL end as [Database]
FROM sys.dm_sql_referenced_entities(N'dbo.AgeofRptds','OBJECTS')

This query is telling me what database is being used, what table is being used and what columns are being queried .

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

Regards
Farhan Jamil

Answer 1

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.

Answer 2

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.