Find the stored procedures and agents that use Linked Server

Today I had a scenario where I had to identify all the stored procedures and agents that used a particular Linked Server.  We are thinking of getting rid of these multiple  Linked Servers and combining them into one server.

So I have used the sys.sql_modules table to find out what stored procedures are using these linked servers.

The code I have written is as follows.

SELECT OBJECT_NAME(object_id) object ,  *
      FROM sys.sql_modules
      WHERE
Definition LIKE '%linkedsrv1%'
 OR
Definition Like '%linkedsrv2%'
 OR
 Definition Like '%linkedsrv3%'
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;


I have also used the sysjobsteps and sysjobs tables from msdb database to find out what jobs are using these Linked Servers,

The code I have written is as follows:

  SELECT j.name AS JobName,js.command
      FROM msdb.dbo.sysjobsteps jsteps
         INNER JOIN msdb.dbo.sysjobs jobs
            ON jobs.job_id = jsteps.job_id
      WHERE
 jsteps.command LIKE   '%linkedsrv1%'
 OR
jssteps.command Like  '%linkedsrv2%'
 OR
 jsteps.command Like  '%linkedsrv3%'

Next question that I am pondering is if we consolidate these servers into one server, what are the considerations I need to be aware of.

I will be covering this in a future post.  Meanwhile appreciate some feedback from the readers.