T-SQL query to find the list of objects referring Linked Servers

One of my client reached out to me today. He was expecting a query that should return the list of objects such as Stored Procedures, Functions, Triggers and View, if it’s using the Linked Server.

The query returns the data in format as can be seen in the image below. Query will search by both Linked Server name as well as Data Source such as IP/Host Name.

Following is the query.

SELECT SRV.[name] AS LinkedServerName
	, SRV.[data_source] AS LinkedServerDataSource
	, PRO.[name] AS ObjectName
	, 'Stored Procedure' AS ObjectType
FROM sys.servers SRV
	INNER JOIN sys.procedures PRO
		ON (OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[name] + '%')
			OR OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[data_source] + '%'))
UNION
SELECT SRV.[name] AS LinkedServerName
	, SRV.[data_source] AS LinkedServerDataSource
	, PRO.[name] AS ObjectName
	, 'View' AS ObjectType
FROM sys.servers SRV
	INNER JOIN sys.views PRO
		ON (OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[name] + '%')
			OR OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[data_source] + '%'))
UNION
SELECT SRV.[name] AS LinkedServerName
	, SRV.[data_source] AS LinkedServerDataSource
	, PRO.[name] AS ObjectName
	, 'Trigger' AS ObjectType
FROM sys.servers SRV
	INNER JOIN sys.triggers PRO
		ON (OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[name] + '%')
			OR OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[data_source] + '%'))
UNION
SELECT SRV.[name] AS LinkedServerName
	, SRV.[data_source] AS LinkedServerDataSource
	, PRO.[name] AS ObjectName
	, 'Function' AS ObjectType
FROM sys.servers SRV
	INNER JOIN sys.objects PRO
		ON (OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[name] + '%')
			OR OBJECT_DEFINITION(PRO.[object_id]) LIKE ('%' + SRV.[data_source] + '%'))
WHERE PRO.[type] in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT');

One thought on “T-SQL query to find the list of objects referring Linked Servers

  1. Very nice.

    I modified it to add joins to sys.objects so I could get the schema name.

    Please note that it will find any substring in the text of a procedure, view, function, etc. that matches a server name. Including in comments. Not a big concern, but it will cause false hits.

    And if you have server names that are substrings of other server names, it will report multiple lines with both names. For example, if there are linked servers SRVA and SRVAA, both names will be reported for an object that has a linked server to SRVAA.

    But all in all, a useful query. Thank you.

    Liked by 1 person

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s