What is the best way to get rows from different databases?

Question

I have 10 identical databases.

I get the database names at runtime.

I want to store rows into a collection of objects.

I also only want one hit on the database server.

My current approach:-

In a query (no stored procedures for X reason) I get list of databases and store in a temporary table.

Then I iterate through each database and create a dynamic query and execute it.

DECLARE @MaxRownum int SET @MaxRownum = (SELECT MAX(RowNum) FROM #Databases) 
DECLARE @Iter int SET @Iter = 1
WHILE @Iter <= @MaxRownum
BEGIN
DECLARE @Database varchar(255) SELECT @Database = Databases FROM #Databases 
      WHERE RowNum = @Iter
IF HAS_DBACCESS(@Database) > 0
    BEGIN 
      //appending query
END
SET @Iter = @Iter + 1
END
EXEC(@Query)

Can I use Linq + entity framework with one hit to server, without dynamic query and without hampering the performance? Is there any better solution?

What are the columns in the #temp table? Can you explain the difference between `@Database (Databases)` and `@DatabaseName (Name)`? What is the actual query you're running against each database? Can you explain the reasons behind no stored procedures? — Aaron Bertrand, May 15 '12 at 22:57
Thanks. #temp table is #Databases and has list of databases. sry i removed @databasename its not required. I am using join on 2 tables using SELECT. I cannot use stored procedure because I have to give execute access to all the users which is time consuming... Thanks — om471987, May 15 '12 at 23:39
Giving users execute rights on a single stored procedure is more time consuming than giving them explicit select rights on this table in all 10 databases? That's interesting. :-) — Aaron Bertrand, May 15 '12 at 23:48
Sry guys I was AFK.... Ppl already have select access. Yes every database is under same server — om471987, May 16 '12 at 12:30

score 1 · Accepted Answer · answered May 15 '12 at 23:46

Having no idea what your query is (I asked but you did not supply it), and not sure that you understand it is going to be extremely difficult to supply database names as variables without a "dynamic query", here is a much simpler way to do it IMHO:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'UNION ALL'
    --// you will need to fill in your "//appending query" stuff here:
    + ' SELECT ... FROM ' + QUOTENAME(Databases) + '.dbo.tablename'
 FROM #Databases
 WHERE HAS_DBACCESS(Databases) = 1;

SET @sql = STUFF(@sql, 1, 9, '');

EXEC sp_executesql @sql;

What is the best way to get rows from different databases?

1 Answers1