This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here.

This article came about due to a request to find if any stored procedures are using any of the linked servers.  In addition to finding if any stored procedures may be using a linked server was the need to find the name of that procedure.  The request evolved to also include finding any SQL Agent jobs that may be using the linked server.

In response to that request, I had the idea to adapt a query I had recently written.  I will be posting that in the near future.

Query

Code block

DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name AS name
FROM sys.servers
WHERE is_linked = 1
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.sql_modules
WHERE Definition LIKE '%'+@VName +'%'
AND OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 ;
 
FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT j.name AS JobName,js.command
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j
ON j.job_id = js.job_id
WHERE js.command LIKE '%'+@VName +'%'
FETCH NEXT FROM Findlinked INTO @VName;
END
 
CLOSE Findlinked
DEALLOCATE Findlinked

Don’t blast me just yet due to the use of a loop (cursor).  In a case such as what has just been presented, a cursor is a legitimate tool.  I need to find all stored procedures that contain the text I specify.  In this case, I am searching for each of the linked servers.

In addition to searching all of the stored procedures for the use of a defined linked server, I am re-opening the same cursor to search all of the jobs defined on the server.  I do this in the event that the job was created with an ad-hoc query in lieu of using a stored procedure.

If you run the query, you will find that it should provide a quicker turnaround time on documenting the use of the linked servers than manually searching.