How can I tell what database/procedure is using a linked server?

Question

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL 2005 which we're trying to eliminate. Server C has copies of some of the databases from Server B, and we're migrating applications from Server B's database copies to Server C's.

When I'm on Server B, I can see connections from Server A to a certain database but I don't know how to tell what procedure, task, or job from Server A is using that linked server connection to Server B.

In order to retire the database on Server B, I need to re-point Server A's connections to a database on Server C; but in order to do that, I need to know what procedures, tasks, or jobs on Server A are using that connection so they can be updated.

Is there a way to see the dependencies on a linked server without disabling the linked server to see what starts failing?

• paparazzo: Kind of hacky but I wonder if you could not just create a linked server on C with the proper name that points back to itself. In DNS you can have two names point to the same IP.

Answer 1

Well, you could parse the procedures (as long as they aren't encrypted) and jobs on server A looking for the name of the linked server - but keep in mind these requests could be passed in through an app, coming in from ad hoc SQL, built dynamically, etc. so this won't catch everything. This can also produce false positives if the linked server name is a common term that might be naturally found in code or comments.

DECLARE @sql NVARCHAR(MAX) = N'', 
 @p NVARCHAR(MAX), 
 @linked_server SYSNAME = N'%linked_server_name%';
SET @p = N' UNION ALL SELECT N''$db$'', s.name,
 o.name FROM $db$.sys.sql_modules AS p
 INNER JOIN $db$.sys.objects AS o
 ON p.[object_id] = o.[object_id]
 INNER JOIN $db$.sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 WHERE p.definition LIKE @lsn';
SELECT @sql = @sql + REPLACE(@p, N'$db$', QUOTENAME(name))
 FROM sys.databases; -- may want to filter out system dbs, offline, etc
SET @sql = STUFF(@sql, 1, 11, N'') + N';';
EXEC sys.sp_executesql @sql, N'@lsn SYSNAME', @linked_server;
SELECT j.name FROM msdb.dbo.sysjobs AS j
 INNER JOIN msdb.dbo.sysjobsteps AS s
 ON j.job_id = s.job_id
 WHERE s.command LIKE @linked_server;

Of course you may need to look for a synonym instead first:

DECLARE @sql NVARCHAR(MAX) = N'', 
 @p NVARCHAR(MAX), 
 @synonym_prefix SYSNAME = N'[linked_server_name].%';
SET @p = N' UNION ALL SELECT N''$db$'', s.name, o.name
 FROM $db$.sys.synonyms AS o
 INNER JOIN $db$.sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 WHERE s.base_object_name LIKE @spre';
SELECT @sql = @sql + REPLACE(@p, N'$db$', QUOTENAME(name))
 FROM sys.databases; -- may want to filter out system dbs, offline, etc
SET @sql = STUFF(@sql, 1, 11, N'') + N';';
EXEC sys.sp_executesql @sql, N'@spre SYSNAME', @synonym_prefix;

Then use those synonym names instead of 'linked_server_name' above.

• Fredric Shope: Thanks, I eventually got this to work (I had to iron out an odd collation issue that popped up) and it revealed 19 SProcs across 8 different databases. Cheers!
• Aaron Bertrand: @FredShope oh yes, sorry, I always forget to put in the disclaimer about matching collations across databases.
• Fredric Shope: Nah, I couldn't expect you to do that, I'm just not used to mismatches like this so it took some time to identify. Inherited servers with lax standards are so much fun!

Answer 2

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects.

/*********************************
 Name: Text Searcher
 Author: Jonathan Fite
 Created: 1/16/2015
 Purpose: To enable one to more easily search for objects by an indicated string.
 Usage: 
 - Update the "Where To Search" section to turn on or off various sections.
 - Craft an insert statement to populate "@ObjectsToSearchFor" with the text you
 want to look for. 
 - Execute.
 NOTE: This works recursively (in a fashion). The first execution gets just what you are
 looking for. But if you execute it again without explicitly dropping the "#SearchResults" table
 then it will run again, but this time looking for references to the objects it found in the first pass.
 This is useful when you are trying to find out what is using a linked server. So you get the name of the 
 linked server (and it's host name) and run a search on that. It tells you a handful of objects. You then run
 it again, but this time looking for those objects (say, in other stored procedures or in SSIS/Jobs). 
 CAUTION: If you want to use the recursive functionality, you will need to save off the results FIRST. 
 DROP TABLE #SearchResults
 -Linked Server Query (finds all references to linked servers as configured, as well as all variations
 that would allow for accessing a remote server without using a linked server.
 INSERT INTO @ObjectsToSearchFor
 (TextString)
 SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME 
 UNION
 SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
 UNION
 SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
 UNION
 SELECT 'OPENROWSET'
 UNION
 SELECT 'OPENQUERY'
 UNION 
 SELECT 'OPENDATASOURCE'
**********************************/
--To hide row counts and make the print statements easier to see.
SET NOCOUNT ON
/** Where To Search **/
DECLARE @SearchSSIS BIT = 1
DECLARE @SearchObjectNames BIT = 1
DECLARE @SearchObjectDefinition BIT = 1
DECLARE @SearchAgentJobs BIT = 1
/** What are you looking for? **/
DECLARE @ObjectsToSearchFor TABLE
 (
 TextString VARCHAR(200) NULL
 )
INSERT INTO @ObjectsToSearchFor
(TextString)
SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME 
UNION
SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
UNION
SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
UNION
SELECT 'OPENROWSET'
UNION
SELECT 'OPENQUERY'
UNION 
SELECT 'OPENDATASOURCE'
/*************************************************************************************************************************
 Shouldn't need to mess with anything below this line.
**************************************************************************************************************************/
/** Handle Recursion on #SearchResults.
 Remember to save your data first!
 If you don't want recursion, explitly drop #SearchResults between executions.
 **/
IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
BEGIN
 DELETE FROM @ObjectsToSearchFor 
 --This allows for successive iterations to search for objects which reference objects which reference....
 INSERT INTO @ObjectsToSearchFor
 (TextString)
 SELECT DISTINCT ObjectName
 FROM #SearchResults 
 WHERE LocationFound IN ('sys.sql_modules')
 AND ObjectName IS NOT NULL 
 SET @SearchObjectNames = 0
 DROP TABLE #SearchResults 
END
CREATE TABLE #SearchResults
 (
 DatabaseName SYSNAME NULL
 , ObjectName SYSNAME NULL
 , LocationFound VARCHAR(100) NULL
 , MatchedOn VARCHAR(200) NULL
 , Misc VARCHAR(100) NULL
 )
/** Various declarations to hold results. **/
DECLARE @SearchTerm VARCHAR(200)
DECLARE @DatabaseName SYSNAME
DECLARE @Query NVARCHAR(4000)
-- Loop through search terms.
DECLARE curSearch CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT O.TextString
 FROM @ObjectsToSearchFor O
OPEN curSearch
FETCH NEXT FROM curSearch
INTO @SearchTerm
WHILE @@FETCH_STATUS = 0
BEGIN
 --Begin Searching.
 PRINT ('Looking for: ' + @SearchTerm)
 /** This will loop over every database, only do it if you need it. **/
 IF(@SearchObjectDefinition = 1 OR @SearchObjectNames = 1)
 BEGIN
 DECLARE curDatabases CURSOR LOCAL STATIC FORWARD_ONLY
 FOR SELECT D.name
 FROM sys.sysdatabases D
 ORDER BY D.name 
 OPEN curDatabases
 FETCH NEXT FROM curDatabases 
 INTO @DatabaseName 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 --Look for object name matching...
 IF(@SearchObjectNames = 1)
 BEGIN
 SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
 + 'INSERT INTO #SearchResults'
 + ' SELECT DB_NAME()'
 + ', O.name'
 + ', ' + QUOTENAME('sys.objects', '''')
 + ', ' + QUOTENAME(@SearchTerm, '''')
 + ', O.type_desc'
 + ' FROM sys.objects O (NOLOCK)'
 + ' WHERE LOWER(O.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')
 EXEC sp_executesql @Query 
 SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
 + 'INSERT INTO #SearchResults'
 + ' SELECT DB_NAME()'
 + ', C.name'
 + ', ' + QUOTENAME('sys.columns', '''')
 + ', ' + QUOTENAME(@SearchTerm, '''')
 + ', O.name + O.type_desc'
 + ' FROM sys.columns C (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = C.[object_id]'
 + ' WHERE LOWER(C.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')
 EXEC sp_executesql @Query 
 END --Looking for object names.
 --Look for definitions.
 --Find objects that have the string in question listed, but ignore where the name is what is being searched for.
 IF(@SearchObjectDefinition = 1)
 BEGIN
 SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
 + 'INSERT INTO #SearchResults'
 + ' SELECT DB_NAME()'
 + ', O.name'
 + ', ' + QUOTENAME('sys.sql_modules', '''')
 + ', ' + QUOTENAME(@SearchTerm, '''')
 + ', O.type_desc'
 + ' FROM sys.sql_modules M (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = M.[object_id]'
 + ' WHERE LOWER(M.[definition]) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')
 + ' AND NOT(LOWER(O.name) = LOWER(' + QUOTENAME(@SearchTerm, '''') + '))'
 EXEC sp_executesql @Query 
 END --Looking for object definitions.
 --Get next database
 FETCH NEXT FROM curDatabases
 INTO @DatabaseName
 END
 CLOSE curDatabases
 DEALLOCATE curDatabases 
 END
 --Look in SSIS packages.
 IF(@SearchSSIS = 1)
 BEGIN
 ;WITH SSISFolders AS
 (
 SELECT pf.folderid
 , pf.parentfolderid
 , CAST(pf.foldername AS VARCHAR(MAX)) AS foldername
 FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
 WHERE pf.parentfolderid IS NULL 
 UNION ALL
 SELECT pf.folderid
 , pf.parentfolderid
 , Parent.foldername + '\' + ISNULL(CAST(pf.foldername AS VARCHAR(MAX)), '') AS foldername
 FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
 INNER JOIN SSISFolders Parent ON Parent.folderid = pf.parentfolderid 
 )
 INSERT INTO #SearchResults
 SELECT NULL
 , ISNULL(F.foldername, '') + '\' + CAST(ISNULL(S.name, '') AS VARCHAR(MAX))
 , 'msdb.dbo.syssispackages'
 , @SearchTerm
 , NULL 
 FROM msdb.dbo.sysssispackages S (NOLOCK)
 LEFT OUTER JOIN SSISFolders F (NOLOCK) ON F.folderid = S.folderid 
 WHERE LOWER(CAST(CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS VARCHAR(MAX))) LIKE ('%' + @SearchTerm + '%')
 END
 --Look in SQL Agent Jobs
 IF(@SearchAgentJobs = 1)
 BEGIN
 --Job Name
 INSERT INTO #SearchResults
 SELECT NULL
 , J.name
 , 'msdb.dbo.sysjobs'
 , @SearchTerm
 , 'Job Name'
 FROM msdb.dbo.sysjobs J (NOLOCK)
 WHERE LOWER(J.name) LIKE ('%' + @SearchTerm + '%')
 --Job Description
 INSERT INTO #SearchResults
 SELECT NULL
 , J.name
 , 'msdb.dbo.sysjobs'
 , @SearchTerm
 , 'Job Description'
 FROM msdb.dbo.sysjobs J (NOLOCK)
 WHERE LOWER(J.[description]) LIKE ('%' + @SearchTerm + '%')
 --Step Name.
 INSERT INTO #SearchResults
 SELECT NULL
 , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
 , 'msdb.dbo.sysjobsteps'
 , @SearchTerm 
 , 'Step Name'
 FROM msdb.dbo.sysjobsteps S (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
 WHERE LOWER(S.step_name) LIKE ('%' + @SearchTerm + '%')
 --Step Code.
 INSERT INTO #SearchResults
 SELECT NULL
 , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
 , 'msdb.dbo.sysjobsteps'
 , @SearchTerm 
 , 'Step Code'
 FROM msdb.dbo.sysjobsteps S (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
 WHERE LOWER(S.command) LIKE ('%' + @SearchTerm + '%')
 END --End Looking at Jobs.
 --Get next search term.
 FETCH NEXT FROM curSearch
 INTO @SearchTerm
END
--Cleanup.
CLOSE curSearch
DEALLOCATE curSearch 
/***************** Display Output **********************************/
--Display search terms.
SELECT O.TextString
 , COUNT(S.MatchedOn) AS MatchCount 
FROM @ObjectsToSearchFor O
 LEFT OUTER JOIN #SearchResults S ON S.MatchedOn = O.TextString
GROUP BY O.TextString 
--Display search results.
SELECT DISTINCT * FROM #SearchResults