Find Linked Server Usage in SQL

Very useful script if wanting to know where Linked Servers are being called:

SET NOCOUNT ON;
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 @VName, OBJECT_NAME(OBJECT_ID) 
        FROM sys.sql_modules 
        WHERE Definition LIKE '%'+@VName +'%' 
        AND (OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 
        OR OBJECTPROPERTY(OBJECT_ID, 'IsView') = 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
Advertisements

One Response to Find Linked Server Usage in SQL

  1. David says:

    Anyone know how I could modify this to repeat for each database on a server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: