Hi,
You might have working on store procedures which contains thousands of lines in Sql server, it is difficult to get list of tables, views procedures used in the store procedure. The below code will perform to Show the list of tables, views, functions in a given object (procedure, function, etc.), this code works for SQL Server 2008 and above versions.


To get list of Tables used in store procedure:

SELECT DISTINCT
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.your_store_procedure', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U')


To get list of Functions used in store procedure:

SELECT DISTINCT
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.your_store_procedure', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('FN')

To get list of Views used in store procedure:

SELECT DISTINCT
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.your_store_procedure', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('V')


To get list of Procedure used in store procedure:


SELECT DISTINCT
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.your_store_procedure', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('P')


System.objects Catalog:

AF = Aggregate function (CLR)
FT = Assembly (CLR) table-valued function
C  = CHECK constraint
IF = SQL inline table-valued function
D  = DEFAULT (constraint or stand-alone)
IT = Internal table
F  = FOREIGN KEY constraint
P  = SQL Stored Procedure
FN = SQL scalar function
PC = Assembly (CLR) stored-procedure
FS = Assembly (CLR) scalar-function
PG = Plan guide
PK = PRIMARY KEY constraint
SQ = Service queue
R  = Rule (old-style, stand-alone)
TA = Assembly (CLR) DML trigger
RF = Replication-filter-procedure
TF = SQL table-valued-function
S  = System base table
TR = SQL DML trigger
SN = Synonym
TT = Table type
U  = Table (user-defined)
V  = View
UQ = UNIQUE constraint
X  = Extended stored procedure