Show cross-database dependencies between objects

Question

Show cross-database dependencies between objects

DBA Cabuloso

SSCommitted

Points: 1733
More actions
April 2, 2013 at 8:46 am

#394506

Hello all,
I use SQL Server 2008 R2. In the SSMS there is a nice funcionality that shows both "Objects that depend on ..." and "Objects on which ... depends". The latter one works fine, even referencing objecs that are located in other databases, but the first ("Objects that depend on "...") does not work if it is referenced by an object in other database.
Is there any way to find out what objects depend on one table, for example, that is referenced by views, for example, in another database? I have tested the sys.dm_sql_referencing_entities but it also didn't work.
I think it is possible to do it, because, if it works for objects on which .... depends, theoretically, it is possible to do the other way.
Thanks in advance
DBA Cabuloso
Lucas Benevides
________________
DBA Cabuloso
Lucas Benevides

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

nope, a database doesn't keep track of what references Itself, only what objects it's own objects references.

for same server references, you could query each database and stick the results into a temp table; for cross server items, you'd have to repeat on each potential server.

/* --Results

DBName TheSchema TheObject referenced_server_name referenced_database_name referenced_schema_name referenced_entity_name type_desc ColumnName

master dbo myTallyCalendar NULL NULL NULL TallyCalendar USER_TABLE NULL

master dbo sp_helpweb NULL master dbo spt_values USER_TABLE NULL

*/

SELECT

DB_NAME() as DBName,

OBJECT_SCHEMA_NAME(depz.referencing_id) As TheSchema,

OBJECT_NAME(depz.referencing_id) As TheObject,

depz.referenced_server_name,

depz.referenced_database_name,

depz.referenced_schema_name,

depz.referenced_entity_name,

objz.type_desc,

colz.name AS ColumnName

FROM sys.sql_expression_dependencies depz

INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id

LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id

AND colz.column_id = referencing_minor_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBA Cabuloso SSCommitted Points: 1733 More actions · Answer 2

Thank you Lowell,

Your script has helped me, but it doesn't bring what I want. If you make the INNER JOIN with the referenced_id, it just returns the dependencies that are already identified, which in most cases for CROSS-DATABASES dependencies, does not work.

But I studied the view sys.sql_expression_dependencies and made the following script. (The fields names are in portuguese)

ALTER PROCEDURE dbo.insereDependencias

AS

BEGIN

DECLARE @command VARCHAR(5000)

SET @command = 'Use [' + '?' + '] ;' + CHAR(10) + CHAR(13);

SET @command += 'IF db_name() NOT in (''master'', ''msdb'', ''tempdb'', ''model'', ''distribution'', ''distribution1'', ''ReportServer'', ''ReportServerTempDB'' )

SELECT @@SERVERNAME AS ServidorReferente,

DB_NAME() AS DB_Referente,

OBJECT_SCHEMA_NAME(depz.referencing_id) As SchemaReferente,

OBJECT_NAME(depz.referencing_id) As ObjetoReferente,

objz.type_desc as TipoObjetoReferente,

ISNULL(referenced_server_name, @@SERVERNAME) as ServidorReferenciado,

ISNULL(referenced_database_name, DB_NAME()) as DatabaseReferenciado,

ISNULL(referenced_schema_name, OBJECT_SCHEMA_NAME(depz.referencing_id)) as SchemaReferenciado,

referenced_entity_name as ObjetoReferenciado,

ISNULL(obj2.type_desc, ''(n/d)'') as TipoObjetoReferenciado,

CURRENT_TIMESTAMP AS DtaConsulta

FROM sys.sql_expression_dependencies depz

INNER JOIN sys.objects objz on depz.referencing_id = objz.object_id

LEFT OUTER JOIN sys.objects obj2 on obj2.object_id = depz.referenced_id

WHERE referencing_minor_id = 0 ' --I don't want to retrieve computed columns

TRUNCATE TABLE mydb.dbo.DependenciasBD

INSERT INTO mydb.dbo.DependenciasBD

(ServidorReferente

,DB_Referente

,SchemaReferente

,ObjetoReferente

,TipoObjetoReferente

,ServidorReferenciado

,DatabaseReferenciado

,SchemaReferenciado

,ObjetoReferenciado

,TipoObjetoReferenciado

,DtaConsulta)

EXEC sp_MSForEachDB @command

END;

It worked fine. Later I will do it to run as a job with target and extend this to multiserver dependencies.

Thanks a lot!

________________
DBA Cabuloso
Lucas Benevides

rmerckel SSC Enthusiast Points: 117 More actions · Answer 3

your script does not return any column names they are all NULL

rmerckel SSC Enthusiast Points: 117 More actions · Answer 4

your script does not return any column names they are all NULL