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

4 thoughts 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.

   LikeLiked by 1 person

   Reply

2. The code depends on free text searching for linked server names in stored procedure definitions.
   This is absolutely unsafe. In my case this method found 70 procedures or views referring to linked servers.
   The true number is 14.

   Try this instead:

   SELECT
   O1.type_desc AS object_type,
   OBJECT_NAME(referencing_id) AS EntityName,
   referenced_server_name as RefdServer,
   referenced_database_name as RefdDatabase,
   referenced_schema_name as RefdSchema,
   O2.type_desc as RefdEntityType,
   referenced_entity_name as RefdEntity
   FROM
   sys.sql_expression_dependencies AS sed
   INNER JOIN sys.objects AS O1 ON sed.referencing_id = O1.object_id
   LEFT OUTER join sys.objects AS O2 ON sed.referenced_entity_name = O2.name
   WHERE NOT
   referenced_entity_name in (‘dtproperties’,’sysdiagrams’)

   LikeLike

   Reply

   1. Many thanks Richard for posting this!

      SQL Server is such an ocean. I was seriously not aware of sys.sql_expression_dependencies. Thanks to you that I know it now!

      I tried your query on my local system with one entry in sys.servers but it didn’t served my purpose. It gave me the table references but not the linked server references. Am I missing something?

      LikeLike

      Reply

      1. Hi Brahmanand

         The field RefdServer will contain the name of the linked server.

         Example:
         CREATE VIEW [dbo].[vwsnowinventoryRegUser]
         AS
         SELECT cl.UserName, cl.UserId, sw.AppId, cl.ClientId, cl.HostName AS DeviceId, sw.InstallDate, app.Manufacturer, app.Name, app.Version, app.IsRegistry, cl.LastUpdate
         FROM RAMCPHSQL01.SnowInventory.inv.datasoftware AS sw INNER JOIN
         RAMCPHSQL01.SnowInventory.inv.Dataclient AS cl ON cl.ClientId = sw.ClientId INNER JOIN
         RAMCPHSQL01.SnowInventory.inv.Dataapplication AS app ON sw.AppId = app.ApplicationId AND app.IsRegistry = 1 AND cl.UserName NOT LIKE ‘%NoLoggedOnUser’

         Query:
         SELECT
         O1.type_desc AS object_type,
         OBJECT_NAME(referencing_id) AS EntityName,
         referenced_server_name as RefdServer,
         referenced_database_name as RefdDatabase,
         referenced_schema_name as RefdSchema,
         O2.type_desc as RefdEntityType,
         referenced_entity_name as RefdEntity
         FROM
         sys.sql_expression_dependencies AS sed
         INNER JOIN sys.objects AS O1 ON sed.referencing_id = O1.object_id
         LEFT OUTER join sys.objects AS O2 ON sed.referenced_entity_name = O2.name
         WHERE NOT
         referenced_entity_name in (‘dtproperties’,’sysdiagrams’)
         and OBJECT_NAME(referencing_id) = ‘vwsnowinventoryRegUser’

         Result:
         object_type EntityName RefdServer RefdDatabase RefdSchema RefdEntityType RefdEntity
         VIEW vwsnowinventoryRegUser RAMCPHSQL01 SnowInventory inv Dataclient
         VIEW vwsnowinventoryRegUser RAMCPHSQL01 SnowInventory inv datasoftware
         VIEW vwsnowinventoryRegUser RAMCPHSQL01 SnowInventory inv Dataapplication

         Please note that the query is not able to find the entity type (RefdEntityType) of the entities on the linked server. I don’t know what I am missing.

         Best regards, Richard

         LikeLike