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 , *
Definition LIKE '%linkedsrv1%'
Definition Like '%linkedsrv2%'
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
jsteps.command LIKE '%linkedsrv1%'
jssteps.command Like '%linkedsrv2%'
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.