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