Finding databases with no dependencies

Question

Post reply

Finding databases with no dependencies

webrunner

SSC-Dedicated

Points: 31713

March 10, 2020 at 7:38 pm

#3732992

Hello experts,

This is more of a conceptual question - I'm pretty sure I can do all of the legwork once I get pointed in the right direction.

How does one go about finding which databases on a server have no dependencies? I realize this may not be totally possible because some developer might have code somewhere with a cross-database query.

But for starters, I just want find a list of databases that have no cross-dependencies with other databases on the same server. My goal is to be able to have confidence in planning to migrate some databases without worrying about breaking dependent code.

I was able to use the following procedure as a starting point. But although it is helpfully comprehensive, I don't think it is sufficient to filter for, say, WHERE referencing_database <> referenced_database because a cross-database dependency can be in either direction. I'm sure I am overthinking this, which is why I wanted to run it by the experts here for feedback.

Thanks for any help!

-- webrunner

https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
database_id int, 
database_name sysname
);

INSERT INTO #databases(database_id, database_name)
SELECT database_id, [name]
FROM sys.databases
WHERE 1 = 1
AND [state] <> 6 /* ignore offline DBs */
AND database_id > 4; /* ignore system DBs */

DECLARE 
@database_id int, 
@database_name sysname, 
@sql varchar(max);

CREATE TABLE #dependencies(
referencing_database varchar(max),
referencing_schema varchar(max),
referencing_object_name varchar(max),
referenced_server varchar(max),
referenced_database varchar(max),
referenced_schema varchar(max),
referenced_object_name varchar(max)
);

WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
SELECT TOP 1 @database_id = database_id, 
@database_name = database_name 
FROM #databases;

SET @sql = 'INSERT INTO #dependencies select 
DB_NAME(' + convert(varchar,@database_id) + '), 
OBJECT_SCHEMA_NAME(referencing_id,' 
+ convert(varchar,@database_id) +'), 
OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), 
referenced_server_name,
ISNULL(referenced_database_name, db_name(' 
+ convert(varchar,@database_id) + ')),
referenced_schema_name,
referenced_entity_name
FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

EXEC(@sql);

DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

I have an alternative approach for you to consider. It relies on the following assumptions:

All of your databases are scripted and in source control
Cross-database references use three-part names (DB.Schema.Object)

If the above conditions are both true, it's relatively simple to do a regex search across all files and all databases, looking for text of the form " a.b.c ", where a, b and c are strings containing one or more characters.

This method is attractive because it's simple and does not tax the database engine.

Take just one more step, doing regex replace, and you are well on your way to being able to perform refactoring in bulk.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

The referencing_id would always be the current db, I would think.

You should check the referenced_server_name as well as the db name. It's possible that local db1 could point to the same db name on a different server (instance).

You'll probably also want to include data from sys.dm_sql_referenced_entities.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

many years (sql 2000) ago I did something similar - it is horrible and should not be repeated

sp_msforeachdb 'select ''?'', name from ?.dbo.sysobjects where object_definition(id) like ''%.%.%'' '

do not try this at home kids

MVDBA