从多个数据库创建视图，这些数据库由具有数据库名称的列链接 [英] Creating view from multiple databases linked by columns with db name

问题描述

我需要创建一个视图或存储过程，该视图或存储过程结合数据并使用包含模式（db）名称的列从同一服务器上的3个不同数据库返回结果集。

Hi I need to create a view or stored procedure that combines data and returns a result set from 3 different databases on the same server using a column that holds a schema (db) name.

例如，在第一个数据库上，我有此表：

For Example on the first DB I have this table:

CREATE TABLE [dbo].[CloudUsers](
    ID int IDENTITY(1,1) NOT NULL,
    Username nvarchar(50) NULL,
    MainDB nvarchar(100) NULL
) ON [PRIMARY]

每个CloudUser都有一个单独的数据库，因此接下来我需要使用MainDB名称从User数据库中获取数据。我需要的数据总是1行，因为我正在使用聚合函数/查询。

Each CloudUser has a separate DB so next now I need to fetch the data from the User database using the MainDB name. The data I need is always 1 row cause I'm using aggregate functions / query.

因此在User MainDB中，假设我有此表。

So in the User MainDB let's say I have this table.

CREATE TABLE [dbo].[CLIENT](
   ID int NOT NULL,
   Name nvarchar(50) NULL,
   ProjectDBName [nvarchar](100) NULL
   CreationDate datetime NULL
) ON [PRIMARY]

我查询如下：

select min(CreationDate) from MainDB.Client

对客户端的相同想法我需要从指向客户端ProjectDBName的第三个数据库中获取更多数据。再次汇总数据：

The same Idea for the Client I need to fetch even more data from a 3rd database that points to the Client ProjectDBName. Again it's aggregate data:

select Count(id) as TotalTransactions from ProjectDBName.Journal

我的最终结果应该包含所有数据库中的记录。这是我需要统计的只读数据。

My final result should have records from all databases. It's readonly data that I need for statistics.

最终结果集示例：

CloudUsers.Username，MainDB- > CreationDate，ProjectDBName-> TotalTransaction

CloudUsers.Username, MainDB->CreationDate, ProjectDBName->TotalTransaction

如何实现？

推荐答案

对于那些对如何解决此问题感到好奇的人，我发现了自己的解决方案，使用了一些游标+动态变量和一个简单的表变量，请尽情享受。

For you who are curious of how to solve this issue I have found my own solution using some cursors + dynamic and a simple table variable, enjoy.

ALTER PROCEDURE CloudAnalysis as
DECLARE @objcursor cursor
DECLARE @innercursor cursor
DECLARE @userid int
       ,@maindb nvarchar(100)
       ,@clientid int
       ,@name nvarchar(50)
       ,@projdb nvarchar(100)
       ,@stat nvarchar(50)
       ,@sql nvarchar(max)
       ,@vsql nvarchar(max)
       ,@rowcount int


DECLARE @result table(userid int,clientid int,maindb nvarchar(100),name nvarchar(50),projdb nvarchar(100),stat nvarchar(50))

SET @objcursor =  CURSOR FORWARD_ONLY STATIC FOR SELECT c.id,c.maindb,u.client_id FROM dbo.ClientUsers c join dbo.UserClients u on c.id = u.user_id  open @objcursor

FETCH NEXT FROM @objcursor INTO @userid,@maindb,@clientid
WHILE (@@FETCH_STATUS=0)
BEGIN

IF (EXISTS (SELECT name 
FROM master.dbo.sysdatabases 
WHERE ('[' + name + ']' = @maindb 
OR name = @maindb)))
BEGIN

    set @sql = N'SELECT @name = c.name,@projdb=c.ProjectDBName FROM ' + @maindb + '.dbo.CLIENT c WHERE c.id = ' + cast(@clientid as nvarchar)


    EXECUTE sp_executesql @sql, N'@name NVARCHAR(50) OUTPUT,@projdb NVARCHAR(100) OUTPUT', 
    @name = @name OUTPUT
    ,@projdb = @projdb OUTPUT

    SELECT @rowcount = @@ROWCOUNT

    IF @rowcount > 0
    BEGIN

    --print '    client: ' + cast(@clientid as nvarchar)+
    --':' + @name + ' projdb: ' + @projdb

    IF (EXISTS (SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE ('[' + name + ']' = @projdb 
    OR name = @projdb)))
    BEGIN

    SET @sql = N'SELECT @stat = j.stat FROM ' + @projdb + '.dbo.JournalTransaction j'

    EXECUTE sp_executesql @sql
    ,N'@stat NVARCHAR(50) OUTPUT'
    ,@stat = @stat OUTPUT

    END

    INSERT INTO @result (userid,clientid,maindb,name,projdb,stat)
    VALUES (@userid,@clientid,@maindb,@name,@projdb,@stat)

    END

END

FETCH NEXT FROM @objcursor INTO @userid,@maindb,@clientid


END

CLOSE @objcursor
DEALLOCATE @objcursor 

SELECT * FROM @result

这篇关于从多个数据库创建视图，这些数据库由具有数据库名称的列链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！