SSMS v18及以上RPC禁用时如何删除远程服务器多前缀永久表

核心思路 #

绕过本地DROP TABLE的前缀数量限制、RPC禁用限制，将删除逻辑放到远程服务器上下文执行，使用OPENQUERY完成操作，该方式不需要开启RPC权限，且所有DDL逻辑在远程端执行无多前缀报错问题。

单表删除实现（带存在判断） #

SELECT * FROM OPENQUERY([remote_server_name], '
SET NOCOUNT ON;
-- 以下逻辑完全在远程服务器执行，无需跨服务器前缀
IF EXISTS (
    SELECT 1 FROM [remote_db_name].sys.tables 
    WHERE name = ''test123'' AND schema_id = SCHEMA_ID(''dbo'')
)
DROP TABLE [remote_db_name].[dbo].[test123];
')

说明： #

• OPENQUERY内部的SQL语句直接在remote_server_name对应服务器执行，删除语句仅用到2个前缀，符合SQL Server语法限制
• 无需调用跨服务器存储过程，完全避开RPC禁用的限制
• 可兼容SSMS v18及以上所有版本，无SQL Server版本兼容问题

批量删除指定前缀表实现 #

如果需要删除多个带统一前缀的表，可直接在OPENQUERY内部构造动态SQL批量执行：

SELECT * FROM OPENQUERY([remote_server_name], '
SET NOCOUNT ON;
DECLARE @delete_sql NVARCHAR(MAX) = N'''';
-- 筛选所有dbo架构下、前缀为test_的表，可自行调整筛选规则
SELECT @delete_sql += N''DROP TABLE [remote_db_name].[dbo].'' + QUOTENAME(name) + N'';''
FROM [remote_db_name].sys.tables
WHERE schema_id = SCHEMA_ID(''dbo'') AND name LIKE ''test[_]%'';

-- 执行批量删除，此处调用的是远程本地的sp_executesql，无RPC限制
IF @delete_sql <> N''''
EXEC sp_executesql @delete_sql;
')

原方案失败原因说明 #

• 直接执行四部分命名的DROP TABLE报错：SQL Server本地执行DROP TABLE时，对象名最多允许2个前缀（即[库].[架构].[表]三段），四部分命名（服务器.库.架构.表）共3个前缀，超出语法限制
• 跨服务器调用sp_executesql报错：跨服务器执行存储过程需要开启链接服务器的RPC OUT权限，环境禁用RPC时无法执行该操作

内容的提问来源于stack exchange，提问作者unseen_rider