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.
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.