Dynamic Joining Across Databases I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is updated every time certain data from the client databases are updated. The 'Updates' table is setup somewhat like this:

DbName     CustomerId   
ClientA    1           
ClientB    1           


I would like to join this data to a table in the database denoted in the 'DbName' column and end up with something like this:

DbName     CustomerId     CustomerName
ClientA    1              Bob Jones
ClientB    1              John Smith


What is the cleanest and most efficient solution for this problem?
In order to help you with the dynamic SQL, I've put together a small proof-of-concept:

USE Test; /* this could be TempDB */

CREATE TABLE DatabaseNames /* this is the table that exists in your 
                                  "separate" database */
(
    DatabaseID INT NOT NULL CONSTRAINT PK_DatabaseNames_DatabaseID 
                PRIMARY KEY CLUSTERED
    , ClientName NVARCHAR(255) NULL
    , TargetTableName NVARCHAR(255) NULL
    , SomeValue NVARCHAR(255) NULL
);

INSERT INTO DatabaseNames (DatabaseID, ClientName, TargetTableName, SomeValue)
SELECT d.database_id, d.name, 'TestTable', 'Whatever'
FROM sys.databases d
WHERE d.database_id&gt;4;

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);

SET @cmd = '';
SET @sep = '';

SELECT @cmd = @cmd + @sep + 'SELECT * FROM TestDB.dbo.DatabaseNames DN 
         INNER JOIN ' + QUOTENAME(DB_NAME(DatabaseID)) + '.dbo.' + 
         QUOTENAME(TargetTableName) + ' CN ON DN.SomeValue = CN.SomeValue;'
        , @sep = CHAR(13) + CHAR(10)
FROM DatabaseNames; 

SELECT @cmd; /* this will display the generated dynamic SQL */

/* uncomment this to execute the dynamic SQL
EXEC sp_executesql @cmd;
*/