How to identify objects used in a stored procedure

Recently there was a requirement from a project team to identify all objects belong to a stored procedure. Actually they wanted only the tables used in a stored procedure.You can use sp_depends with the SP and then you will be able to get all the dependent objects. But the actual requirement was much more complex than that because they wanted to analyze a trace they created for all the SPs captured in the trace.

Sp_depends and sys.sysdepends are provided in SQL Server 2008 for backward compatibility purpose only. So not recommending to use in new developments.

Refer below links for more information.


sp_depends


sys.sysdepends

SQL Server 2008 introduced two dynamic management functions to replace sp_depends.


sys.dm_sql_referencing_entities


sys.dm_sql_referenced_entities

I have created two scripts, 1) to use in SQL Server 2000 and 2005 2) to use in SQL Server 2008 and later versions.

Script 1

create table #db_list
(
database_name sysname
)


create table #stored_procedures
(
id int identity(1,1),
database_name sysname,
sp_name sysname
)


create table #table_column_list
(
id int identity(1,1),
database_name sysname,
sp_name sysname,
depid int, 
dependent_obj_name sysname,
object_type char(5),
column_name sysname null,
column_id int,
data_type sysname null
)


declare @file_path varchar(255)
--set the trace file name and the path here.
set @file_path='C:\Test.trc'


insert #db_list (database_name)
SELECT distinct databasename FROM sys.fn_trace_gettable ( @file_path , default ) tb
inner join sys.trace_events te 
on tb.EventClass=te.trace_event_id
where tb.EventClass not in (45,41)
order by databasename


declare @database_name sysname
declare @RWCnt int
declare @strSQL nvarchar(max)
select top 1 @database_name=database_name from #db_list order by database_name
set @RWCnt=@@ROWCOUNT
while @RWCnt>0
begin


truncate table #stored_procedures

--get the object names which needs to be analysed
insert #stored_procedures (sp_name,database_name)
SELECT distinct ObjectName,DatabaseName FROM sys.fn_trace_gettable ( @file_path , default ) tb
inner join sys.trace_events te 
on tb.EventClass=te.trace_event_id
where tb.EventClass not in (45,41)
and DatabaseName=@database_name
and ObjectName is not null
order by ObjectName

set @strSQL=N'use ' + @database_name + '

--identifying dependent objects
insert #table_column_list (database_name,sp_name,depid,dependent_obj_name,object_type,column_name,column_id,data_type)
select distinct  sp.database_name
,sp.sp_name
,sd.depid
,object_name(sd.depid) as dependent_obj_name 
,CASE OBJECTPROPERTY(sd.depid,''IsUserTable'') WHEN 1 THEN ''U'' ELSE 
CASE OBJECTPROPERTY(sd.depid,''IsProcedure'') WHEN 1 THEN ''P'' ELSE
CASE OBJECTPROPERTY(sd.depid,''IsTableFunction'') WHEN 1 THEN ''TF'' ELSE
CASE OBJECTPROPERTY(sd.depid,''IsScalarfunction'') WHEN 1 THEN ''SF'' ELSE
CASE OBJECTPROPERTY(sd.depid,''IsInlineFunction'') WHEN 1 THEN ''IF''
ELSE ''N/A''
END
END
END
END
END
,sc.name as Column_Name
,sc.column_id
,st.name as Data_type  
from sys.sysdepends sd
inner join #stored_procedures sp 
on object_name(sd.id)=sp.sp_name
left join sys.columns sc 
on sd.depid=sc.object_id
left join sys.types st
on sc.system_type_id=st.system_type_id and st.name in (''varchar'',''char'',''text'')
order by sp.sp_name,object_name(sd.depid),sc.column_id

--keeping only non-unicode columns
delete #table_column_list where data_type is null'

exec sp_executesql @strSQL

--get next db
select top 1 @database_name=database_name from #db_list 
where database_name>@database_name
order by database_name


set @RWCnt=@@ROWCOUNT


end
go
--final select
select * from #table_column_list


--drop temp tables 
drop table #db_list
drop table #stored_procedures
drop table #table_column_list


Script 1



create table #db_list
(
database_name sysname
)


create table #stored_procedures
(
id int identity(1,1),
database_name sysname,
sp_name sysname,
schemaid int,
objectid int
)


create table #table_column_list
(
id int identity(1,1),
cross_server_name sysname null,
cross_database_name sysname null,
referenced_schema_name sysname null,
referenced_entity_name sysname,
referenced_databasename sysname,
referenced_minor_name sysname null,
referenced_class_desc nvarchar(60),
column_name sysname null,
data_type sysname null
)




declare @file_path nvarchar(255)
--set the trace file name and the path here.
set @file_path='C:\Test-1.trc'


insert #db_list (database_name)
SELECT distinct databasename FROM sys.fn_trace_gettable ( @file_path , default ) tb
inner join sys.trace_events te 
on tb.EventClass=te.trace_event_id
where tb.EventClass not in (45,41)
order by databasename




declare @database_name sysname
declare @RWCnt int
declare @strSQL nvarchar(max)
select top 1 @database_name=database_name from #db_list order by database_name
set @RWCnt=@@ROWCOUNT
while @RWCnt>0
begin


truncate table #stored_procedures

set @strSQL=N'

--get the object names which needs to be analysed
insert #stored_procedures (sp_name,database_name,ObjectID,schemaid)
SELECT distinct tb.ObjectName,tb.DatabaseName,tb.ObjectID,so.schema_id FROM sys.fn_trace_gettable (@file_path , default ) tb
inner join sys.trace_events te 
on tb.EventClass=te.trace_event_id
inner join '+ @database_name + '.sys.objects so
on tb.ObjectID=so.object_id
where tb.EventClass not in (45,41)
and DatabaseName=@database_name
and ObjectName is not null'

exec sp_executesql @strSQL, N'@file_path nvarchar(255),@database_name sysname',@file_path=@file_path,@database_name=@database_name

print @strSQL


set @strSQL=null


set @strSQL=N'

insert #table_column_list
(
cross_server_name
,cross_database_name
,referenced_databasename
,referenced_schema_name
,referenced_entity_name
,referenced_minor_name
,referenced_class_desc
,column_name
,data_type
)
SELECT re.referenced_server_name
 ,re.referenced_database_name
 ,@database_name 
 ,re.referenced_schema_name
 ,re.referenced_entity_name
 ,re.referenced_minor_name
 ,re.referenced_class_desc
 ,sc.name
 ,st.name as Data_type
FROM #stored_procedures sp
cross apply sys.dm_sql_referenced_entities (schema_name(sp.schemaid) +''.''+sp.sp_name, ''OBJECT'') re
left join sys.columns sc
on re.referenced_minor_name=sc.name and re.referenced_id=sc.object_id
left join ' + @database_name + '.sys.types st
on sc.user_type_id=st.user_type_id and st.name in (''varchar'',''char'',''text'')
where sp.database_name=@database_name'


print @strSQL
exec sp_executesql @strSQL, N'@database_name sysname',@database_name=@database_name

--get next db
select top 1 @database_name=database_name from #db_list 
where database_name>@database_name
order by database_name


set @RWCnt=@@ROWCOUNT


end
go


--final select
select * from #table_column_list


--drop temp tables 
drop table #db_list
drop table #stored_procedures
drop table #table_column_list


I created these scripts specially for a project requirement and you can customize them to match your requirement.