【mssql】如何快速查找链接服务器被那些对象引用

最新推荐文章于 2023-09-08 09:17:28 发布

TheDarkStar

最新推荐文章于 2023-09-08 09:17:28 发布

阅读量94

点赞数 1

文章标签：服务器 sqlserver 数据库

本文链接：https://blog.csdn.net/qq_27786597/article/details/132418969

版权

有时候原始环境不是自己搭建的，但是需要快速查看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');