Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In preparation for migrating a server farm to the cloud, I am attempting to enumerate all the linked servers that have been defined over the years. I also need to find (and correct) the code that references these linked servers.

Here is something I threw togetether to help identify names of objects that refer to the linked server, but this only works against stored procs, views, functions and triggers:

declare @sql varchar(2000)
select  @SQL = 'use ?;
print ''?'';
print replicate(''-'',len(''?''));
print '' ''; 

select object_name(sc.id)
  from syscomments sc
  where text like ''%TheLinkedServer%''
'

exec sp_msforeachdb @sql

I know I will have to crack open the DTS/SSIS packages...I am just trying to get as many finished as quickly as possible.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

To query for jobs, you just need to look in msdb's job tables:

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

Also syscomments is a backward compatibility view - it is not the best way to search for references in objects, particularly because any object > 4000 characters will be stored in multiple rows and could, potentially, have the linked server name straddle two rows. Much safer would be:

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

And finally, sp_msforeachdb is completely unreliable (see here and here).

Here is a search procedure I wrote last year that may be useful:

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

(Taken from this blog post.)

It relies on a split function like this one (or a better one, if you already have one):

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
   );
share|improve this answer
    
Thanks Aaron. Looks like you put a lot of work into that proc. I forgot to mention the server is SQL2000, which is why I am using syscomments. –  datagod Sep 27 '12 at 2:57
    
@datagod ah yep, that's an important distinction. Glad the first bit helped at least. –  Aaron Bertrand Sep 27 '12 at 3:01
add comment

If you know what you're searching for, Red Gate has a really good free search tool called SQL Search that is a plugin to SSMS. It's easier and less error prone than any attempts I've made at looking through syscomments. Give it a shot. Download it here.

When searching for references within jobs make sure you include msdb in the search.

If you want to query SQL Agent jobs directly then you can use this:

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.