mssql-如何快速查找链接服务器被那些对象引用-转载

前言

有时候原始环境不是自己搭建的，但是需要快速查看SQL server中的链接服务器被哪些对象引用，可以使用以下语句。

查找链接服务器被那些对象引用

原文地址

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

```