时间戳

首页 > 解决方案 > 从多个数据库中获取数据 - 动态数据库

sql-server - 从多个数据库中获取数据 - 动态数据库

问题描述

我们将 SQL Server 2014 Enterprise 与许多数据库一起使用。我必须使用EXACT SAME Schema从每个数据库执行查询并获取报告/数据，并且数据库以Cab开头

当在我们的 ERP 项目中添加新公司时，将使用以Cab开头的精确模式创建新数据库，并为其分配递增编号，如下所示：

Cab1
Cab2
Cab3
Cab5
Cab10

我可以得到数据库名称：

SELECT name
FROM master.sys.databases
where [name] like 'Cab%' order by [name]

我必须创建一个存储过程来从每个数据库的表中获取数据。

如何使用存储过程来做到这一点，因为数据库是从Cab开始动态创建的？

标签： sql-serverstored-proceduressql-server-2014

解决方案

如果必须传递参数，可以使用 EXEC(@Statement) 或 EXEC SP_EXECUTESQL。

CREATE OR ALTER PROCEDURE dbo.GetDataFromAllDatabases
AS
BEGIN
    DECLARE @T TABLE (id INT NOT NULL IDENTITY(1, 1), dbName VARCHAR(256) NOT NULL)
    INSERT INTO @T
    SELECT NAME FROM MASTER.SYS.DATABASES WHERE [NAME] LIKE 'Cab%' ORDER BY [NAME]

    CREATE TABLE #AllData (......)

    DECLARE @Id INT, @DbName VARCHAR(128)
    SELECT @Id = MIN(Id) FROM @T
    WHILE @Id IS NOT NULL
    BEGIN
        SELECT @DbName = dbName FROM @T WHERE Id = @Id
        DECLARE @Statement NVARCHAR(MAX)
        SET @Statement = CONCAT(N'INSERT INTO #AllData (...) SELECT .... FROM ', @DbName, '.dbo.[TableName]')
        EXEC(@Statement);
        --YOU CAN USE BELOW LINE TOO IF YOU NEED TO PASS VARIABLE
        --EXEC SP_EXECUTESQL @Statement, '@Value INT', @Value = 128
        SET @Id = (SELECT MIN(Id) FROM @T WHERE Id > @Id)
    END
END

推荐阅读