Let me begin by singing the praises of synonyms.  For those who are unfamiliar with them, think of a synonym as a nickname with directions.  Say, for instance, I have a query on Database A that needs to reach out to Database B for information.  I could do this one of two ways:

Way #1:

CREATE DATABASE Sandbox;
GO

USE Sandbox;
GO

CREATE TABLE dbo.Table1
(
    Column1 NVARCHAR(5),
    Column2 NVARCHAR(5),
    Column3 NVARCHAR(5)
);

GO

CREATE DATABASE SandboxPartDeux;
GO

USE SandboxPartDeux;
GO

CREATE TABLE dbo.Table2
(
    Column1 NVARCHAR(5),
    Column2 NVARCHAR(5),
    Column3 NVARCHAR(5)
);

GO


USE Sandbox;
GO

SELECT T1.Column1,
T1.Column2,
T2.Column3
FROM dbo.Table1 AS T1
INNER JOIN SandboxPartDeux.dbo.Table2 AS T2
ON T2.Column1 = T1.Column1;
GO

 Let’s call this one the wrong way

Way #2:

Now, let’s make a synonym:

CREATE SYNONYM dbo.Table2 FOR SandboxPartDeux.dbo.Table2;

USE Sandbox;
GO

SELECT T1.Column1,
T1.Column2,
T2.Column3
FROM dbo.Table1 AS T1
INNER JOIN dbo.Table2 AS T2
ON T2.Column1 = T1.Column1;
GO

Henceforth and evermore known as, the RIGHT way.  You can drop these databases now.

Why is this the right way? Because if I ever move this table, I only have to change my code in one place, as opposed to using my find the string method.  I’d rather correct something once than who-knows-how-many-times, any day.

The script below searches the metadata for views, sprocs and functions for occurrences of 3 and 4 part names.  Three-part names consist of databasename.schemaname.objectname, and four-part names consist of servername.databasename.schemaname.objectname. Because the code searches metadata, it isn’t always perfect.  If your comments mention a servername followed by a period, for example, it will be caught.  Nevertheless, it’s a great place to begin looking, and a real help in getting rid of problems before they really bite you.

SET NOCOUNT ON;

IF OBJECT_ID('temp..#ServerNames') IS NOT NULL
    DROP TABLE #ServerNames;

IF OBJECT_ID('temp..#BaseTable') IS NOT NULL
    DROP TABLE #BaseTable;

IF OBJECT_ID('temp..#DatabaseNames') IS NOT NULL
    DROP TABLE #DatabaseNames;

IF OBJECT_ID('temp..#4PartNames') IS NOT NULL
    DROP TABLE #4PartNames;

IF OBJECT_ID('temp..#3PartNames') IS NOT NULL
    DROP TABLE #3PartNames;

--Let's make all the tables we will need.  These are small, so I want to load them up into memory and do everything there

CREATE TABLE #ServerNames
(
    ServerName sysname NULL
);

CREATE TABLE #BaseTable -- will hold preliminary results from INFORMATION_SCHEMA tables that we will use to find 3 and 4 part names
(
    DatabaseName sysname NULL,
    SchemaName sysname NULL,
    ObjectName sysname NULL,
    ObjectType sysname NULL,
    ObjectText NVARCHAR(MAX) NULL
);

CREATE TABLE #DatabaseNames
(
    DatabaseName sysname NULL
);


CREATE TABLE #4PartNames -- To hold info on objects using 4 part names
(
    DatabaseName sysname NULL,
    SchemaName sysname NULL,
    ObjectName sysname NULL,
    ObjectType sysname NULL,
    ObjectText NVARCHAR(MAX) NULL
);

CREATE TABLE #3PartNames -- To hold info on objects using 3 part names
(
    DatabaseName sysname NULL,
    SchemaName sysname NULL,
    ObjectName sysname NULL,
    ObjectType sysname NULL,
    ObjectText NVARCHAR(MAX) NULL
);

--Let's start putting data into the temp tables. 

INSERT INTO #ServerNames
(
    ServerName
)
SELECT name
FROM sys.servers
WHERE name <> @@SERVERNAME;


INSERT INTO #DatabaseNames
(
    DatabaseName
)
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND name NOT LIKE 'ReportServer%'
      AND is_read_only = 0
      AND state_desc = 'ONLINE';


--Load the base table that we will derive our objects using 3 and 4 part names from

DECLARE @BseTblName sysname;
DECLARE @BseTblCursor CURSOR;
DECLARE @BseTblsql NVARCHAR(MAX);

SET @BseTblCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT DatabaseName
FROM #DatabaseNames
ORDER BY DatabaseName;

OPEN @BseTblCursor;

FETCH NEXT FROM @BseTblCursor
INTO @BseTblName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @BseTblsql
        = N'USE [' + @BseTblName + N'];
	INSERT INTO #BaseTable
	SELECT '''       + @BseTblName
          + N''' AS DatabaseName, 
	          SPECIFIC_SCHEMA AS SchemaName,
	          SPECIFIC_NAME AS ObjectName,
              ROUTINE_TYPE AS ObjectType,
			  ROUTINE_DEFINITION AS ObjectText
FROM INFORMATION_SCHEMA.ROUTINES 


UNION ALL 

SELECT '''           + @BseTblName
          + N''' AS DatabaseName,  
		      TABLE_SCHEMA AS SchemaName,
			  TABLE_NAME AS ObjectName,
			  ''VIEW'' AS ObjectType,
			  VIEW_DEFINITION AS ObjectText
FROM INFORMATION_SCHEMA.VIEWS
ORDER BY ObjectType, ObjectName';

    PRINT @BseTblsql;
    EXECUTE sp_executesql @BseTblsql;
    FETCH NEXT FROM @BseTblCursor
    INTO @BseTblName;

END;

CLOSE @BseTblCursor;
DEALLOCATE @BseTblCursor;

--Placeholder so we know where we are

SELECT 'Items with possible four-part names included coming in 7....6....5....4....3....2....1....' AS StatusUpdate;

--Now, let's find the 4 part names. 

DECLARE @SrvrSrchName sysname;
DECLARE @SrvrSrchCursor CURSOR;
DECLARE @SrvrSrchSQL NVARCHAR(MAX);

SET @SrvrSrchCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT ServerName
FROM #ServerNames
ORDER BY ServerName;

OPEN @SrvrSrchCursor;

FETCH NEXT FROM @SrvrSrchCursor
INTO @SrvrSrchName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SrvrSrchSQL
        = N'INSERT INTO #4PartNames
				        SELECT DatabaseName,
							   SchemaName,
							   ObjectName,
							   ObjectType,
							   ObjectText
					    FROM #BaseTable
						WHERE ObjectText LIKE ''%' + @SrvrSrchName + N'%''
						OR ObjectText LIKE ''%FROM [' + @SrvrSrchName + N'].%''
						OR ObjectText LIKE ''%JOIN ' + @SrvrSrchName + N'%''
						OR ObjectText LIKE ''%JOIN [' + @SrvrSrchName + N'].'';';
    PRINT @SrvrSrchSQL;
    EXECUTE sp_executesql @SrvrSrchSQL;
    FETCH NEXT FROM @SrvrSrchCursor
    INTO @SrvrSrchName;
END;

CLOSE @SrvrSrchCursor;
DEALLOCATE @SrvrSrchCursor;

SELECT DISTINCT '4PartNameCheck' AS '4PartName',
	   @@SERVERNAME AS ServerName,
       DatabaseName,
       SchemaName,
       ObjectName,
       ObjectType
FROM #4PartNames
ORDER BY DatabaseName,
         ObjectName;

--Placeholder so we know where we are

SELECT 'Items with possible three-part names included coming your way. This one takes a little longer, so be patient!' AS StatusUpdate;

--Now, let's find the 3 part names. 

DECLARE @DBSrchName sysname;
DECLARE @DBSrchCursor CURSOR;
DECLARE @DBSrchSQL NVARCHAR(MAX);

SET @DBSrchCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT DatabaseName
FROM #DatabaseNames
ORDER BY DatabaseName;

OPEN @DBSrchCursor;

FETCH NEXT FROM @DBSrchCursor
INTO @DBSrchName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @DBSrchSQL
        = N'INSERT INTO #3PartNames
				        SELECT DatabaseName,
							   SchemaName,
							   ObjectName,
							   ObjectType,
							   ObjectText
					    FROM #BaseTable
						WHERE ObjectText LIKE ''%FROM ' + @DBSrchName + N'.%''
						OR ObjectText LIKE ''%FROM [' + @DBSrchName + N'].%''
						OR ObjectText LIKE ''%JOIN ' + @DBSrchName + N'%''
						OR ObjectText LIKE ''%JOIN [' + @DBSrchName + N'].'';';
    PRINT @DBSrchSQL;
    EXECUTE sp_executesql @DBSrchSQL;
    FETCH NEXT FROM @DBSrchCursor
    INTO @DBSrchName;
END;

CLOSE @DBSrchCursor;
DEALLOCATE @DBSrchCursor;

SELECT DISTINCT '3PartNameCheck' AS '3PartName',
       @@SERVERNAME AS ServerName,
       DatabaseName,
       SchemaName,
       ObjectName,
       ObjectType
FROM #3PartNames
ORDER BY DatabaseName,
         ObjectName;


DROP TABLE #BaseTable;
DROP TABLE #DatabaseNames;
DROP TABLE #ServerNames;
DROP TABLE #4PartNames;
DROP TABLE #3PartNames;

Here is an example of the output: