Queries On Multiple Databases
April 22, 2001
I work with an accounting system that is currently comprised of 150 databases and growing by three or four databases per year. My coworkers and I frequently need queries to run against some or all of the databases at once. Since the system's report writer is slow and constrained, I've developed both scripts and stored procedures, relying on either unions or cursors, as an alternative.
Unions are straightforward and well-documented in BOL. They can easily reach across multiple databases, and even multiple servers.
If I have three databases with similar schema, and I want a count of rows in a particular table, by database, I could run the following query:
I specify the object I want by using its "fully qualified" name, more commonly called three-part; i.e., NJ1.dbo.apinpchg. NJ1 is the database, dbo is the object's owner, and apinpchg is the object, a table in this case. On my server, the dbo owns every object except INFORMATION_SCHEMA's views, so I could refer to this table as NJ1..apinpchg. Don't assume the dbo owns everything on your server(s) or that there aren't identically named objects owned by different users; there are several counter-examples elsewhere in my company.
One point of interest is MA1, which is on another server, srv2. I access it with four-part syntax. The SA would have had to set up srv2 as a linked server and mapped my local login to a login on srv2 that had SELECT rights on MA1.dbo.apinpchg.
I recommend using
The reader is referred to BOL for criteria for using
I do most of my work with cursors and dynamic SQL. Large unions are tiresome to code, read, and maintain. They may not run at all on 6.5, even if they're within the maximum batch size.
The following stored procedure returns every transaction in every company for the specified GL account for FY2000. It can dump the results into Excel via Microsoft Query. The only way to do this with my system's report writer is to run 150 transaction detail reports and copy and paste them together.
The SP is an example. It doesn't have even rudimentary error checking, and is inflexible.
A few notes. The cursor needs a table of database names to work with. If necessary, you can use sysdatabases in the master database and control the rows with a WHERE. I've also used derived tables occasionally.
This example uses
Unless your users work in ISQL/W or Query Analyzer, you'll likely want to
develop a cursor solution as a stored procedure. Be aware that the SP won't
users rights to databases and objects that they don't already have;
Which to Use?