The following code uses the MSSQL object model to determine what things have dependencies in them. It can check using either specified procs/views that you put into a temp table, or you can tell it to scrape all objects in respect to your database.
It pulls them into an easy to paste into excel format for communicating with users.
/************************************************************************************************************************
Author: Christopher Harsch - themobilefieldbase.blogspot.com
Description: This
creates the object map for the database
this is hard coded, if you want to track more
objects than listed
the stored procedure needs to be updated.
**************************************************************************************************************************/
DECLARE @AllProceduresAndViews int = 1 -- 1 for all proces -- 0 for only procs specified here
-- note
this code is not feature complete, and has hard codings specific to its use.
-- This
code may error because of permission restrictions, but will continue, you can
look at the result set indepdently.
drop table if exists #tmp_tables_depended_on;
create table
#tmp_tables_depended_on
(
ID int identity(1,1)
, ObjectFullName varchar(255)
, referenced_database_name varchar(255)
, referenced_schema_name varchar(255)
, referenced_entity_name varchar(255)
, xType varchar(4)
, isChecked int DEFAULT(0)
)
-- Declare
starting procedure
DECLARE
@sp nvarchar(100)
SET @sp = N''
-- You can
insert the specific objects you want to check
-- or set the
default parameter to 1 above
insert into
#tmp_tables_depended_on
(
ObjectFullName
, referenced_database_name
, referenced_schema_name
, referenced_entity_name
, xType
, isChecked
)
SELECT
'dbo.myproc',
referenced_database_name = null,
referenced_schema_name = 'dbo',
referenced_entity_name = 'myproc',
xType = 'P'
, 0
UNION ALL
SELECT
'dbo.myproc2',
referenced_database_name = null,
referenced_schema_name = 'dbo',
referenced_entity_name = 'myproc2',
xType = 'P'
, 0
insert into
#tmp_tables_depended_on
(
ObjectFullName
, referenced_database_name
, referenced_schema_name
, referenced_entity_name
, xType
, isChecked
)
SELECT
s.name+'.' +s.name,
referenced_database_name = null,
referenced_schema_name = sc.name,
referenced_entity_name = s.name,
xType = s.type
, 0
--select
distinct type
from sys.objects
s
inner join sys.schemas sc
on s.schema_id = sc.schema_id
where (s.name like '%dailyreporting%' Or
@AllProceduresAndViews =1)
and s.type in ('P','F','V','TF')
and not exists (
select 1 from
#tmp_tables_depended_on ttdo
where ttdo.ObjectFullName = s.name+'.' +s.name
)
DECLARE @referenced_schema_name varchar(255)
DECLARE @referenced_entity_name varchar(255)
WHILE ( (
select count(*) from #tmp_tables_depended_on where isChecked =0 and xtype is not null
and referenced_schema_name is not null and referenced_schema_name is not null
and referenced_entity_name <> 'a'
) > 0)
BEGIN
select top 1
@referenced_schema_name = referenced_schema_name
,@referenced_entity_name = referenced_entity_name
from #tmp_tables_depended_on
where isChecked =0 and xtype is not null
and referenced_schema_name is not null and referenced_schema_name is not null
and referenced_entity_name <> 'a'
SET @sp =
@referenced_schema_name + '.' + @referenced_entity_name
PRINT @referenced_schema_name
PRINT @referenced_entity_name
PRINT @sp
insert into
#tmp_tables_depended_on
(
ObjectFullName
, referenced_database_name
, referenced_schema_name
, referenced_entity_name
,xType
, isChecked
)
SELECT
@sp,
referenced_database_name,
referenced_schema_name,
referenced_entity_name,
xType
, case when so.xtype = 'U' or referenced_database_name is not null then 1 else 0 end
isChecked
--select *
FROM sys.dm_sql_referenced_entities(@sp, 'OBJECT') a
left join dbo.sysobjects so
on a.referenced_id = so.id
and so.xtype in ('U','P','v','FN','IF')
where not
exists
(
select *
from #tmp_tables_depended_on t
where
t.ObjectFullName = @sp
and isnull(t.referenced_database_name,'') =isnull(a.referenced_database_name ,'')
and t.referenced_schema_name = a.referenced_schema_name
and t.referenced_entity_name = a.referenced_entity_name
and isnull(t.xType,'') = isnull(so.xType,'')
)
group by
referenced_database_name,
referenced_schema_name,
referenced_entity_name,
xType
insert into
#tmp_tables_depended_on
(
ObjectFullName
, referenced_database_name
, referenced_schema_name
, referenced_entity_name
,xType
, isChecked
)
SELECT
@sp,
referenced_database_name,
referenced_schema_name,
referenced_entity_name,
xType
, case when so.xtype = 'U' or referenced_database_name is not null then 1 else 0 end
isChecked
FROM sys.sql_expression_dependencies a
left join dbo.sysobjects so
on a.referenced_id = so.id
and so.xtype in ('U','P','v','FN','IF')
WHERE referencing_id = OBJECT_ID(@sp)
and not
exists
(
select *
from #tmp_tables_depended_on t
where
t.ObjectFullName = @sp
and isnull(t.referenced_database_name,'') =isnull(a.referenced_database_name ,'')
and t.referenced_schema_name = a.referenced_schema_name
and t.referenced_entity_name = a.referenced_entity_name
and isnull(t.xType,'') = isnull(so.xType,'')
)
group by
referenced_database_name,
referenced_schema_name,
referenced_entity_name,xType
update #tmp_tables_depended_on
set iscHecked =1
where
referenced_schema_name = @referenced_schema_name
and referenced_entity_name
= @referenced_entity_name
--select * from #tmp_tables_depended_on
END
select ObjectFullName
,isnull(referenced_database_name
,DB_NAME()) as referenced_database_name
,referenced_schema_name
,referenced_entity_name
,xType
from #tmp_tables_depended_on
where isChecked = 1
