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

Farhan Jamil 421 Reputation points
2021-02-26T10:33:15.887+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Dan Guzman 9,226 Reputation points
    2021-02-26T11:34:11.577+00:00

    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.

    0 comments No comments

  2. MelissaMa-MSFT 24,186 Reputation points
    2021-03-01T05:26:50.9+00:00

    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.

    0 comments No comments