Existe uma maneira de examinar trabalhos SQL em busca de referências a servidores vinculados?

Para consultar jobs, você só precisa procurar nas msdbtabelas de jobs de:

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 '%TheLinkedServer%';

Além disso, syscomments é uma visão de compatibilidade com versões anteriores - não é a melhor maneira de procurar referências em objetos, principalmente porque qualquer objeto > 4000 caracteres será armazenado em várias linhas e poderia , potencialmente, ter o nome do servidor vinculado em duas linhas. Muito mais seguro seria:

SELECT o.name FROM sys.objects AS o
  INNER JOIN sys.sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE m.definition LIKE '%TheLinkedServer%';

E, finalmente, sp_msforeachdb não é confiável (veja aqui e aqui para uma substituição melhor).

Aqui está um procedimento de pesquisa útil que escrevi no ano passado que pode ser útil:

CREATE PROCEDURE dbo.FindString
   @search_string            NVARCHAR(4000),     
   @database_list            NVARCHAR(MAX) = NULL,
   @case_sensitive           BIT = 0,
   @include_jobs             BIT = 0,
   @include_columns          BIT = 0,
   @include_parameters       BIT = 0,
   @include_system_objects   BIT = 0,
   @include_system_databases BIT = 0
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
       @init_sql  NVARCHAR(MAX),
       @run_sql   NVARCHAR(MAX),
       @dbname    NVARCHAR(128),
       @all_text  NVARCHAR(10),
       @coll_text NVARCHAR(50);

   CREATE TABLE #t
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #j
   (
       [job_name]      SYSNAME,
       [step_id]       INT,
       [step_name]     SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #cp
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [param]         NVARCHAR(128),
       [column]        NVARCHAR(128)
   );

   SELECT
       @all_text = CASE @include_system_objects 
           WHEN 1 THEN N'all_' ELSE N'' END,
       @coll_text = CASE @case_sensitive
       WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END;

   SET @init_sql = N'SELECT 
           [database] = ''$db$'',
           [schema]   = QUOTENAME(s.name),
           [object]   = QUOTENAME(o.name),
           [type]     = o.type_desc,
           o.create_date,
           o.modify_date,
           m.[definition]
       FROM 
           $db$.sys.$all$sql_modules AS m
       INNER JOIN 
           $db$.sys.$all$objects AS o
           ON m.[object_id] = o.[object_id]
       INNER JOIN 
           $db$.sys.schemas AS s
           ON o.[schema_id] = s.[schema_id]
       WHERE 
           m.definition $coll$ 
            LIKE N''%'' + @search_string + ''%'' $coll$;';

   SET @init_sql = REPLACE(REPLACE(@init_sql, 
       '$all$', @all_text), '$coll$', @coll_text);

   SET @search_string = REPLACE(@search_string, '''', '''''');

   DECLARE c CURSOR
       LOCAL STATIC FORWARD_ONLY READ_ONLY
       FOR 
           SELECT QUOTENAME(d.name)
               FROM 
                   sys.databases AS d
               LEFT OUTER JOIN 
                   dbo.SplitStrings_XML(@database_list, N',') AS s
                   ON 1 = 1
               WHERE 
                   (
                     LOWER(d.name) = LOWER(s.Item)
                     OR NULLIF(RTRIM(@database_list), N'') IS NULL
                   )
                   AND d.database_id BETWEEN CASE @include_system_databases 
                   WHEN 1 THEN 1 ELSE 5 END AND 32766
               ORDER BY d.name;
   OPEN c;

   FETCH NEXT FROM c INTO @dbname;

   WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname);

       INSERT #t 
       EXEC sp_executesql 
           @run_sql, 
           N'@search_string NVARCHAR(4000)', 
           @search_string;

       IF @include_columns = 1
       BEGIN
           SET @run_sql = N'SELECT 
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                NULL,
                c.name
            FROM 
                $db$.sys.$all$columns AS c
            INNER JOIN 
                $db$.sys.$all$objects AS o
                ON c.[object_id] = o.[object_id]
            INNER JOIN
                $db$.sys.schemas AS s
                ON o.[schema_id] = s.[schema_id]
            WHERE
                c.name $coll$ 
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, 
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql, 
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       IF @include_parameters = 1
       BEGIN
           SET @run_sql = N'SELECT 
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                p.name,
                NULL
            FROM 
                $db$.sys.$all$parameters AS p
            INNER JOIN 
                $db$.sys.$all$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.name $coll$ 
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, 
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql, 
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       FETCH NEXT FROM c INTO @dbname;
   END

   CLOSE c;
   DEALLOCATE c;

   SELECT 'Objects:';

   SELECT 
       [database],
       [schema],
       [object],
       [type],
       [definition] = CONVERT(XML, '<?query --
           USE ' + [database] + ';' 
           + CHAR(13) + CHAR(10) + 'GO' 
           + CHAR(13) + CHAR(10) + [definition] + ' --?>'),
       first_line = (DATALENGTH(abbrev_def)
           -DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1,
       create_date,
       modify_date
   FROM
   (
       SELECT 
           *, 
           [count] = (DATALENGTH([definition]) 
               - DATALENGTH(REPLACE([definition], @search_string, '')))
               /DATALENGTH(@search_string),
           abbrev_def = SUBSTRING([definition], 1, 
               CHARINDEX(@search_string, [definition]))
       FROM #t
   ) AS x
   ORDER BY [database], [schema], [object];

   IF @include_jobs = 1
   BEGIN
       SELECT 'Jobs:';

       SET @run_sql = N'SELECT 
                job_name = j.name, 
                s.step_id, 
                s.step_name, 
                j.date_created,
                j.date_modified,
                [definition] = s.command
            FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS s
            ON j.job_id = s.job_id
            WHERE s.command $coll$ 
              LIKE ''%'' + @search_string + ''%'' $coll$
            ORDER BY j.name, s.step_id;';

       SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text); 

       INSERT #j EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;

       SELECT 
           job_name,
           step_id,
           step_name,
           [command] = CONVERT(XML, '<?query --
               ' + [definition] + ' --?>'),
           create_date,
           modify_date
       FROM #j;
   END

   IF @include_columns = 1 OR @include_parameters = 1
   BEGIN
       SELECT 'Columns/parameters';

       SELECT 
           [database],
           [schema],
           [object],
           [type],
           [param],
           [column],
           create_date,
           modify_date
       FROM #cp
       ORDER BY [database], [schema], [object], [param], [column];
   END

   DROP TABLE #t, #j, #cp;
END
GO

Ele conta com uma função split como esta (ou sua própria, se você já tiver uma ou, melhor ainda, STRING_SPLIT()se estiver no SQL Server 2016 ou superior e puder lidar com suas limitações ):

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(3)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(NVARCHAR(128), Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'NVARCHAR(128)')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>' 
                    + REPLACE(@List, @Delimiter, '</i><i>') 
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );

Se você sabe o que está procurando, o Red Gate tem uma ferramenta de pesquisa gratuita muito boa chamada SQL Search, que é um plug-in para o SSMS. É mais fácil e menos sujeito a erros do que qualquer tentativa que fiz de examinar os comentários do sistema. Experimente. Faça o download aqui .

Ao pesquisar referências em trabalhos, certifique-se de incluir msdb na pesquisa.

Se você deseja consultar os trabalhos do SQL Agent diretamente, pode usar isto:

USE msdb
SELECT
    j.name AS JobName,
    js.step_id AS StepID,
    js.step_name AS StepName
FROM sysjobsteps js
INNER JOIN sysjobs j ON js.job_id=j.job_id
WHERE js.command LIKE '%TheLinkedServer%'
ORDER BY j.name, js.step_id