This query will show all dependencies in a SQL Server Instance.
if exists (select 1 from sys.objects where name = 'table_depend_staging')
drop table
table_depend_staging
go
create table dbo.table_depend_staging(
referencing_database_name sysname,
referencing_schema_name sysname,
referencing_entity_name sysname,
referencing_object_type varchar(2),
referencing_object_type_desc varchar(100),
referenced_database_name sysname,
referenced_schema_name sysname,
referenced_entity_name sysname,
referenced_object_type varchar(2),
referenced_object_type_desc varchar(100))
go
declare @dbname sysname
declare @sql nvarchar(2000)
declare curs cursor for
select name
from master.sys.databases
open curs
fetch next from curs into @dbname
/******************************************GET
ALL DEPENDENCIES**************************************/
while @@FETCH_STATUS = 0
begin
set @sql = 'use ' + @dbname + '
select
''' +
@dbname + ''' as
referencing_database_name,
s.name as referencing_schema_name,
o.name as referencing_entity_name,
o.type as object_type,
o.type_desc as object_type_desc,
ISNULL(referenced_database_name, ''' + @dbname + ''') as referenced_database_name,
case
when len(referenced_schema_name) = 0
or referenced_schema_name is null then ''dbo''
else referenced_schema_name
end as referenced_schema_name, --often
this is an emtpy string
referenced_entity_name
from sys.sql_expression_dependencies ed
inner join ' +
@dbname + '.sys.objects
o on o.object_id = ed.referencing_id
inner join ' +
@dbname + '.sys.schemas
s on s.schema_id = o.schema_id
where referenced_class not in (12, 13)
--not including 12 (ddl trigger) or 13 (system ddl trigger)'
insert into
dbo.table_depend_staging (referencing_database_name, referencing_schema_name,
referencing_entity_name,
referencing_object_type,
referencing_object_type_desc,
referenced_database_name,
referenced_schema_name, referenced_entity_name)
exec sp_executesql @sql
fetch next
from curs into
@dbname
end
close curs
deallocate curs
declare curs cursor for
select name
from master.sys.databases
open curs
fetch next from curs into @dbname
/******************************************GET
TYPE OF DEPENDENT OBJECTS**************************************/
while @@FETCH_STATUS = 0
begin
set @sql = 'update dbo.table_depend_staging
set referenced_object_type = o.type,
referenced_object_type_desc = o.type_desc
from dbo.table_depend_staging s
inner join ' +
@dbname + '.sys.objects
o on o.name collate SQL_Latin1_General_CP1_CI_AS = s.referenced_entity_name
collate SQL_Latin1_General_CP1_CI_AS
where referenced_database_name = ''' + @dbname + ''''
exec sp_executesql @sql
fetch next
from curs into
@dbname
end
close curs
deallocate curs
go
if exists (select 1 from dbo.sysobjects where
name = 'depend_entity_type')
drop table
depend_entity_type
go
create table dbo.depend_entity_type (
depend_entity_type_id int identity(1,1),
depend_entity_type varchar(2) not null,
depend_entity_type_desc varchar(100) not null)
insert into dbo.depend_entity_type (depend_entity_type, depend_entity_type_desc)
select distinct referenced_object_type, referenced_object_type_desc
from dbo.table_depend_staging
where
referenced_object_type_desc is not null
union
select distinct referencing_object_type, referencing_object_type_desc
from dbo.table_depend_staging
where
referencing_object_type_desc is not null
go
if exists (select 1 from dbo.sysobjects where
name = 'depend_entity')
drop table
depend_entity
go
create table dbo.depend_entity (
depend_entity_id int identity(1,1),
depend_entity_type_id int null,
depend_entity_database sysname null,
depend_entity_schema sysname null,
depend_entity_name sysname not null)
go
insert into dbo.depend_entity (depend_entity_type_id, depend_entity_database,
depend_entity_schema, depend_entity_name)
select distinct e.depend_entity_type_id, referenced_database_name,
referenced_schema_name, referenced_entity_name
from dbo.table_depend_staging s
left outer join dbo.depend_entity_type e on
s.referenced_object_type = e.depend_entity_type
union
select distinct e.depend_entity_type_id, referencing_database_name,
referencing_schema_name,
referencing_entity_name
from dbo.table_depend_staging s
left outer join dbo.depend_entity_type e on
s.referencing_object_type = e.depend_entity_type
go
if exists (select 1 from dbo.sysobjects where
name = 'depend_entity_relationship')
drop table
depend_entity_relationship
go
create table dbo.depend_entity_relationship (
depend_entity_relationship_id
int identity(1,1),
referencing_id int not null,
referenced_id int not null,
reference_not varchar(1024) null)
insert into dbo.depend_entity_relationship (referencing_id, referenced_id)
select e.depend_entity_id as
referencing_id, e2.depend_entity_id
as referenced_id
from dbo.table_depend_staging s
inner join dbo.depend_entity
e on e.depend_entity_database
= s.referencing_database_name
and e.depend_entity_schema
= s.referencing_schema_name
and e.depend_entity_name
= s.referencing_entity_name
inner join dbo.depend_entity
e2 on e2.depend_entity_database
= s.referenced_database_name
and e2.depend_entity_schema
= s.referenced_schema_name
and e2.depend_entity_name
= s.referenced_entity_name
select r.depend_entity_relationship_id,
e_referencing.depend_entity_database +
'.' +
e_referencing.depend_entity_schema + '.' + e_referencing.depend_entity_name
as referencing_object,
t_referencing.depend_entity_type_desc as
referencing_object_type,
e_referenced.depend_entity_database +
'.' +
e_referenced.depend_entity_schema + '.' + e_referenced.depend_entity_name as
referenced_object,
isnull(t_referenced.depend_entity_type_desc,
'REFERENCED OBJECT DOESN''T EXIST') as
referenced_object_type
from dbo.depend_entity_relationship r
inner join dbo.depend_entity
e_referenced on e_referenced.depend_entity_id = r.referenced_id
inner join dbo.depend_entity
e_referencing on e_referencing.depend_entity_id = r.referencing_id
inner join dbo.depend_entity_type
t_referencing on t_referencing.depend_entity_type_id =
e_referencing.depend_entity_type_id
left outer join dbo.depend_entity_type t_referenced on t_referenced.depend_entity_type_id
= e_referenced.depend_entity_type_id
