Finding databases with no dependencies

Question

Post reply

Finding databases with no dependencies

webrunner

SSC-Dedicated

Points: 31862

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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101095 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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