Introduction

It's one of those days. The notice comes down, all pink and covered in red ink. Upper management has decided to lower the axe on some poor database. "Terminated," they say, and don't think another thing about it.

But you do. You grab for your coffee mug and find your hand is shaking. Chills shudder across the back of your neck, sweat beads across your forehead, and deep in your heart you know it's not that simple. There could be objects hidden all over the place that need that database. Sure, everyone knows how to track down stored procedures and functions, but what about the other stuff? The stuff no one ever notices until it suddenly isn't working anymore? Kill that database and you could kill your entire infrastructure. What's a DBA to do?

That's a situation I found myself in, once upon a time. "It's just a user database," the boss said. "Only one team, only two people on that team, ever uses it. Just drop it. It'll be fine." Me, I find myself taking comments like that with a barrel of salt. I'd rather know what I'm getting into before I murder some innocent data storage structure, wouldn't you? So here's the thing, there's a way to verify the casualty count before you do the deed and I'm gonna share it with you.

Basic Object Searching

Keep in mind that we only care about external dependents in other databases, so we'll be searching every database but the soon-to-be-departed and tempdb. Rather than have a result set for every database, I'm going to keep all my data in one table on an Admin database (called DBA_Admin) specifically created for maintenance and database metrics. This way I don't have to scroll down past NULL results or save each result set to a separate file. Everything will be in one table. Here's the script to create that central table.

DROP DATABASE [DBA_Admin]
GO
CREATE DATABASE [DBA_Admin]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBA_Admin_Data', FILENAME = N'C:\SQLServer\Data\DBA_Admin_Data.MDF',
  SIZE = 5418368KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB )
 LOG ON 
( NAME = N'DBA_Admin_Log', FILENAME = N'C:\SQLServer\Log\DBA_Admin_Log.LDF', 
  SIZE = 14080KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO
--These numbers are based off an existing database in my environment. 
--Change the database file sizes to suit your needs

IF (SELECT OBJECT_ID('DBA_Admin..DBChk')) IS NOT NULL
 DROP TABLE DBA_Admin..DBChk;
CREATE TABLE DBA_Admin..DBChk (ServerName VARCHAR(100) NOT NULL,
 DBName VARCHAR(100) NOT NULL, ObjectName VARCHAR(100) NOT NULL, 
 ObjectType CHAR(2) NULL, SchemaID INT NULL, SchemaName VARCHAR(100) NULL,
 ObjectDefinition VARCHAR(MAX) NOT NULL);
 --Create a table for storage of results

Dangerous Waters Ahead

Now that we've got our storage table, we're going to collect information on every stored procedure, function, view and anything else in the sys.objects catalog view that might have a reference back to the database we're going to drop. I'm using the msdb system database in my code for two reasons. One, everyone has msdb on their instances. Two, I'm only doing SELECTs at this point. But, you know, dropping system databases is a really bad idea. So make sure to do a mass find-and-replace to change msdb to the user database of your choice before running any of this yourself. Otherwise, the results you get back won't be the ones you expect.

In the below code, I am using the undocumented Microsoft stored procedure sp_MSForEachDB. If you don't know what it is or how it works, you will want to Google it. It's a pretty nifty proc that has served me well a number of times. Also, I'm joining sys.objects to catalog view sys.modules, which usually contains the text information for the proc or functions.

SET QUOTED_IDENTIFIER OFF;
 --So I don't have to count my single quotes
 --Dynamic T-SQL below that auto searches the databases
 -- and disregards my "user" database msdb and Tempdb
EXEC sp_MSforeachdb @Command1 = 'IF "?" NOT IN ("msdb","tempdb")
BEGIN
USE [?];
INSERT INTO DBA_Admin..DBChk
 (ServerName, DBName, ObjectName, ObjectType, SchemaID, SchemaName, ObjectDefinition)
SELECT @@SERVERNAME, DB_NAME(), so.name, so.type, so.schema_id,
 SCHEMA_NAME(so.schema_id), sm.definition 
FROM sys.objects so
INNER JOIN sys.sql_modules sm
ON so.object_id = sm.object_id
WHERE sm.definition LIKE "%msdb%"
--ORDER BY so.type, so.name
END';
--The INSERT weeds out the databases with no information, makes everything easier to read.
--FYI: I use the ORDER BY statement for testing/troubleshooting.
--Comment out the INSERT lines before running with ORDER BY

To Cursor or Not to Cursor

Now the thing is, there are people who say sp_MSforeachdb actually skips databases or items. I've never had that issue (that I'm aware), but for those people who are little skittish using undocumented code (and who can blame you when it might ghost on you at any moment), here's a cursor version of the code which will loop through all the databases.

SET QUOTED_IDENTIFIER OFF;
DECLARE @DBID INT,
 @MySQL VARCHAR(8000),
 @MyDBName VARCHAR(100);
DECLARE DBSearch CURSOR FAST_FORWARD FOR
SELECT database_id
FROM sys.databases
WHERE state_desc = "ONLINE"
 AND name NOT IN ("msdb","tempdb");
OPEN DBSearch;
FETCH NEXT FROM DBSearch INTO @DBID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MyDBName = DB_NAME(@DBID);
SELECT @MySQL = 'USE ' + QUOTENAME(@MyDBName) + '; ' 
+ 'INSERT INTO DBA_Admin..DBChk (ServerName, DBName, ObjectName, ObjectType, SchemaID, SchemaName, ObjectDefinition) '
+ 'SELECT @@SERVERNAME, DB_NAME(), so.name, so.type, so.schema_id, SCHEMA_NAME(so.schema_id), sm.definition '
+ 'FROM sys.objects so INNER JOIN sys.sql_modules sm ON so.object_id = sm. object_id '
+ 'WHERE sm.definition LIKE "%msdb%"';
  
    EXEC(@MySQL);

FETCH NEXT FROM DBSearch INTO @DBID;
END
CLOSE DBSearch;
DEALLOCATE DBSearch;
GO

Because I've found that the sys.sql_modules view doesn't always catch everything, here's a cursor that uses syscomments. Just be aware of the following:

1. syscomments is an older system view that is due to be sacrificed on the altar of SQL Server improvements sometime in the future. So if this bit stops working in the future, don't be surprised.
2. Also, syscomments.text is confined to 4000 characters, so large stored procedures and functions will get cut off. If the database name reference is past character 4000, you’re out of luck on this search.
3. The BOL entry says “Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure…" but I’ve also found user functions listed there.

SET QUOTED_IDENTIFIER OFF;
DECLARE @DBID INT,
 @MySQL VARCHAR(8000),
 @MyDBName VARCHAR(100);
DECLARE DBSearch CURSOR FAST_FORWARD FOR
SELECT database_id
FROM sys.databases
WHERE state_desc = "ONLINE"
 AND name NOT IN ("msdb","tempdb");
OPEN DBSearch;
FETCH NEXT FROM DBSearch INTO @DBID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MyDBName = DB_NAME(@DBID);
SELECT @MySQL = 'USE ' + QUOTENAME(@MyDBName) + '; '  
+ 'INSERT INTO DBA_Admin..DBChk (ServerName, DBName, ObjectName, ObjectDefinition) '
+ 'SELECT @@SERVERNAME, DB_NAME(), OBJECT_NAME(id), text FROM syscomments WHERE text LIKE "%msdb%";';
  
    EXEC(@MySQL);

FETCH NEXT FROM DBSearch INTO @DBID;
--FETCH NEXT FROM DBSearch
END
CLOSE DBSearch;
DEALLOCATE DBSearch;
GO

Then again, maybe you don’t like cursors. Maybe you’d rather have something set-based. Well, fortunately for us all, Sean Lange has contributed this non-cursor set-based script to do the looping without an actual loop.

DECLARE @sql nvarchar(max) = '', @ValueToFind varchar(100) = 'msdb';
--No INSERT needed since this doesn't produce NULL result sets between legitimate result sets
select @sql = @sql + 
'SELECT @@SERVERNAME as ServerName,
''' + d.name + ''' as DatabaseName, so.Name collate SQL_Latin1_General_CP1_CI_AS,
so.type, so.schema_id, SCHEMA_NAME(so.schema_id),
sm.definition collate SQL_Latin1_General_CP1_CI_AS
FROM ' + quotename(d.name) + '.sys.objects so 
INNER JOIN ' + quotename(d.name) + '.sys.sql_modules sm ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE ''%' + @ValueToFind + '%'' union all ' 
from sys.databases d
where d.name not in ('msdb', 'tempdb');
set @SQL = stuff(@sql, len(@sql) - 9, 11, ''); --removes the last union all
       
--select @sql;
exec sp_executesql @sql;

So that is the code for finding basic stored procedures, functions, and other objects. Once you’ve run it, then it’s just a matter of selecting the data and using it for additional research.

SELECT ServerName, DBName, ObjectName, ObjectType, SchemaID, SchemaName, ObjectDefinition
FROM DBA_Admin..DBChk;
--Let's see what we've got

And if you need to search multiple instances, you can just alter your query window to SQLCMD Mode (see picture 1 to know where to find it) and put the below command in front of your code. Do a copy and paste, changing the instance names and you should be able to run everything without much of a problem. Though, I advise running this code one instance at a time so you remember what you’ve looked at and so you don’t have to make too many alterations to the code if something goes south.

:CONNECT MyInstanceName

Picture 1 - SQLCMD

NOTE: Hugo Kornelis recommends the registered servers window for querying multiple instances. Connect to the server group, run the query, and it executes on all instances. A good reason to keep the @@SERVERNAME and DB_NAME() in all the queries is so you can keep track of what object is on what server / database during a mass-execute like this.

Synonyms

I don’t usually use synonyms, but that's no excuse for not checking them. Synonyms can hide in other databases on the same or different instances. So even if you’re sure your company doesn’t use them, it’s a good idea to check for them just in case. All we need to do is tweak either the cursor code (shown below) or the sp_MSforeachdb code and we have a handy solution for finding those sneaky syns.

DECLARE @sql nvarchar(max) = '', @ValueToFind varchar(100) = 'msdb';
select @sql = @sql + 
'SELECT @@SERVERNAME as ServerName, ''' + d.name + ''' as DatabaseName,
 ss.name collate SQL_Latin1_General_CP1_CI_AS,
 ss.type, ss.schema_id, SCHEMA_NAME(ss.schema_id),
 ss.base_object_name collate SQL_Latin1_General_CP1_CI_AS
FROM ' + quotename(d.name) + '.sys.synonyms ss 
WHERE ss.base_object_name LIKE ''%' + @ValueToFind + '%'' union all ' 
from sys.databases d
where d.name not in ('msdb', 'tempdb');
set @SQL = stuff(@sql, len(@sql) - 9, 11, ''); --removes the last union all
       
--select @sql;
exec sp_executesql @sql;

Conclusion

As you can see, this little hunt isn’t too complicated. It’s more a matter of knowing where to look and how to keep track of what you find.

But wait… We’re not done yet. There are a few other things we should check first, like jobs, which might have database references in the job steps. Or SSIS packages which might have connection managers and EXECUTE T-SQL tasks with references, or even linked servers. So stay tuned for Part 2 of this particular detective story, because it gets really interesting from here.

Author’s Notes: First, I need (and want) to thank to all my beta readers: Thom A, Hugo Kornelis, Lynn Pettis, and Sean Lange. Their input made these articles much better than originally written, even if I didn’t take every suggestion offered. That said, all mistakes in this article are mine and mine alone.

Second, I work in a case-insensitive environment and tend to code that way. My beta-readers caught that problem, so I re-wrote everything for case-sensitive databases. If I missed something, I apologize for any headaches it may cause.