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

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?

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

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

Then use those synonym names instead of 'linked_server_name' above.', 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

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

%%CODEBLOCK_1%%

Then use those synonym names instead of 'linked_server_name' above., 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:

%%CODEBLOCK_1%%

Then use those synonym names instead of 'linked_server_name' above.', 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

Then use those synonym names instead of 'linked_server_name' above.', 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

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

%%CODEBLOCK_1%%

Then use those synonym names instead of 'linked_server_name' above., 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:

%%CODEBLOCK_1%%

Then use those synonym names instead of 'linked_server_name' above., 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.', 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

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

%%CODEBLOCK_1%%

Then use those synonym names instead of 'linked_server_name' above., 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:

%%CODEBLOCK_1%%

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

StackExchange Database Administrators Q#127613, answer score: 7