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

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.

    Liked by 1 person

  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’)

    Like

    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?

      Like

      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

        Like

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