对数百个相同的数据库使用一组存储过程

Question

我继承了一个项目，其中每个"客户"都有自己的数据库。有数百个数据库。当前未使用存储过程。

这里使用数据的最佳做法是什么？我是否将存储过程保存在"master"数据库中，并使用动态SQL处理数据？似乎应该有更好的方法。我不想让一个作业在数百个DB周围运行，以保持所有存储过程的同步。

这个动态SQL正在工作，但我想要一个更好的方法。

CREATE PROCEDURE [Users_SELECT] 
@DataBase nvarchar(20),
@UserID uniqueidentifier
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = ''
SET @sql += 'SELECT * FROM ' + @DataBase + '.dbo.Users u '
SET @sql += 'WHERE u.UserID=@UserID '
EXEC sp_executesql @sql, N'@UserID uniqueidentifier', @UserID
END

我尝试了EXEC sp_executesql 'USE ' + @DataBase + '; GO'，然后运行SELECT，但我无法实现。

Answer 1

我看不出将存储过程部署到每个数据库有什么问题。

您还必须部署对模式的更改，因此希望您有适当的基础设施来自动执行。如果没有，现在是时候建立基础设施了。

Answer 2

我想明白了。这可能会让SQL专家非常恼火，但我对这个解决方案很满意。

我在"master"中创建了存储过程。我知道这通常是不允许的。然后我使用

USE master
EXEC sys.sp_MS_marksystemobject [sp_Users_SELECT]
GO

将每个存储过程标记为系统对象。这样，在不同的上下文中可以调用它们。然后我可以这样做：

USE DataBase200
EXEC sp_Users_SELECT

并且它将在DataBase200数据库的上下文中运行，而不必实际将存储过程部署到该数据库。到目前为止效果很好。请记住，必须在存储过程名称前面加上sp_，才能将它们识别为系统对象。

我希望这能帮助到别人。