Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm dealing with a situation in SQL Server 2008 where each client is setup as their own database, with each database having identical table structures.

I have a query that selects from across ~20 different tables within a database, and inserts the records into a reporting database. What we'd like to do is repeat this process for each database, so that all reporting information across all the client databases are then contained in a consolidated reporting database.

The "easy" way would be to just copy/paste the code and do a find/replace all on the database names to have the script execute across all clients. Is there any better way than that though?

share|improve this question
    
Sure, use dynamic SQL and concatenate the database name each time. mssqltips.com/sqlservertip/2201/… –  Aaron Bertrand Jan 23 at 21:18
    
possible duplicate of How to execute SQL against all DBs on a Server –  Kin Jan 23 at 21:20
    
@Kin Ah but the accepted answer there is so wrong today, given what can happen with sp_MSforeachdb. –  Aaron Bertrand Jan 23 at 21:21
    
I disagree this is a duplicate of that question anyway -- this has broader scope. –  Jon Seigel Jan 23 at 21:22
    
@AaronBertrand - Agreed with the use of undocumented SP, but it does have your version of sp - which is more reliable :-) –  Kin Jan 23 at 21:23

2 Answers 2

up vote 6 down vote accepted

This answer assumes you have a master database/table of clients and their associated database names, or some way to figure out which databases in a given instance of SQL Server have relevant databases that you want to query.

Since this is for reporting purposes, you may want to implement some form of snapshotting technique to ensure the data sets consolidate to the same point in time, on at least a per-client basis. As a nice side effect, these techniques usually also alleviate locking/blocking effects of running these types of queries directly against the production data.

Pure Dynamic SQL
Using the master list, build up a SQL statement that uses 3-part names like you're doing now, but instead, inject the database name dynamically. It's unclear from the question what format the data is going to end up in, but it sounds like you want to UNION ALL everything together, which is easily accomplished.

This may be advantageous for you now because it will require very little up-front work.

The downside is that this approach is not very flexible and quickly becomes complicated if you need to run the query when the individual database revisions are different (i.e., the query was tied to a given database revision, and when a database update was deployed, it didn't succeed for all clients). It also may have problems or complications depending on the security model you're using for these databases.

Database-Based Object(s) + Dynamic SQL
This solution creates permanent objects within every database which you can query in an external process. Usually this means a view, stored procedure, or table-valued function. Dynamic SQL will be used to inject the database names into the query as above, but this time all that's needed is to select from, or execute, the database object instead of the raw query directly.

This method gives all the advantages of protecting your code behind an interface just like it would by using views, stored procs, etc., in application code. This also solves the security issues much more easily.

The disadvantage, of course, is that this needs a little more up-front work and management, and that the queried object(s) can only be deployed in database updates, instead of independently.


My personal opinion is that the latter approach is a much better long-term solution, so in this type of scenario, that's the one I would opt for. For ad-hoc types of queries, using the pure dynamic SQL approach is fine.

share|improve this answer
    
+1 but I don't think snapshot isolation guarantees transactional consistency or same point in time across multiple databases. –  Aaron Bertrand Jan 23 at 21:23
1  
The server in question is a fully replicated nightly off of the production server, so that should eliminate any issues related to snapshot isolation. –  John Jan 23 at 21:28
1  
It just feels wrong to me to presume that snapshot isolation guarantees consistency across more than one database. They have different logs, their own transactional scope, and if Availability Groups (which rely on snapshot isolation) can't guarantee transactional consistency across databases... in this specific case, I don't think it matters, but in the general case, I still feel like it's not a promise you can make (even if you have a repro where it doesn't break - that doesn't mean it can't break). –  Aaron Bertrand Jan 23 at 22:20
1  
Likewise, I can't see any mechanism by which you could rely on snapshot isolation to ensure consistency across databases. For that to work you would need support for distributed transactions and snapshot, which we don't have. –  Mark Storey-Smith Jan 23 at 22:30
2  
Was it "don't believe what marketing says about SQL Server features"? :-) –  Aaron Bertrand Jan 23 at 22:53

I realize there is already an accepted answer, but for future reference an alternative is:

If your script doesn't reference the customer database name (i.e. is only schema qualified) then the following batch script will allow you (via sqlcmd) to run it against a list of databases on the localhost without modifying the script.

It will prompt for the file name/path of the SQL script to run and for the name/path of a text file containing a list of databases to execute against (e.g. a simple txt with one db name on each line).

It will log output returned by SQL Server to a log file in the local directory.

@echo off
set /p sql=SQL file:
set /P databases=Database list:

for /F %%d in (%databases%) do (
echo Executing script against %%d
echo ------------------------------------------------------------ >> %sql%.log
echo %%d >> %sql%.log
sqlcmd -h -1 -S localhost -d %%d -i %sql% >> %sql%.log
echo. >> %sql%.log
)

pause

Additional notes:

  • The script assumes Windows Authentication but you can add -U and -P to the sqlcmd line to use SQL Auth
  • If you have a named pipes error you can add ,1433 after localhost to force the use of TCP/IP
  • If you need to run against a remote server you can switch out localhost with the server name/IP
share|improve this answer
    
Thanks, I've already implemented something similar to Jon's second approach, but this looks good too. –  John Jan 28 at 17:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.