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