I've got a server (SQL Server 2005) with multiple archive databases (1 per quarter stretching back 8 years) which are all structurally identical.

I frequently have to query go back over a particular time frame that spans n databases, usually n is small 1-3 but it is possible I have to query the entire set.

Any ideas n the best method of doing this both from the code hygiene along with a performance perspective?

Current solutions are rather ad-hoc, you will find an accumulation of sights that span any just the newest database, other solutions will be to generate dynamic SQL that understands which DB(s) retain the data searched for.

Clearly what you want is always to partition the tables however i can't do that since it is a third party provided database


EDIT: I can not mix the databases being that they are third party controlled, the entire data dimensions are about 50GB so not huge, the biggest tables contain around 1.5m rows per quarter

EDIT2: An information warehouse is certainly the best solution long-term (it's within the plan) however i can't do that today :(

One method to do that: use sp_msForEachDb.

-- Round 1 -------

Refer to this as system procedure having a varchar parameter. (It's really a great deal messier than this, look into the code within the master database if you wish to understand what it is doing.) The parameter should be a slice of dynamic code -- for instance,

DECLARE @DemoParameter varchar(1000)
SET @DemoParameter = 'SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012'''
EXECUTE sp_msForEachDb @DemoParameter

This could run the query against every database around the SQL instance, coming back one set per database -- aside from individuals databases that did not possess the necessary table(s), which may throw a mistake (specially the system databases). This leads us to...

-- Round 2 ---------

Inside the dynamic code, as databases are iterated total cases of the question mark ? is going to be changed using the title from the presently being processed database. This can be used to filter which databases should be processed, and which are not. Note additionally that the "current" database will not be transformed through the routine, you need to do that yourself. This provides us code like:

SET @DemoParameter = '

IF ''?'' like  ''%Foo%''
    USE ?
    SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012''


This could run the query only against individuals databases whose names retain the figures "foo". Possibly you can look for the existence of the table within each database other techniques suggest themselves.

This can shotgun back one dataset for every database, which does not help an excessive amount of if you want them all-in-one neat and orderly data set, which will get us to...

-- Round 3 ------------

Briefly: produce a temp table, and populate it from inside the dynamic query. When I show below, you can the title from the database, and ever the server title -- very helpful whenever your questing for lost data across a large number of databases spread across a number of servers.

Create (or obvious) the temp table:

IF object_id('tempdb.dbo.##Foo') is null
       ServerName         varchar(100)  not null
      ,DBName             varchar(100)  not null

      --  Add your own columns here
      ,MyCol              int  not null

    --Option: Delete this line to not clear on each run

Run the code (this really is my primary template, it is simple to work @DemoParameter in there):

EXECUTE sp_msForEachDB '
IF ''?'' like  ''%Foo%''
    USE ?

    INSERT ##Foo
     select @@servername, db_name()
      from MyTable

...which should create a single temp table together with your data. Test this out, I authored this without really testing the code, and typso will silp in. (#temp tables should work in addition to ##temp, I generally do that with ad-hoc system support issues)

Here's something that will get it done!

@Database varchar(8000),
@Sql varchar(8000)
BEGIN Declare DBName Cursor LOCAL FAST_FORWARD For Choose title FROM sys.databases where title like 'Your_DB_Names%'

Open DBName WHILE (1=1) Begin Fetch Next From DBName into @Database

if @@Fetch_status = -1 Break
if @@Fetch_status = -2 Continue

Set @Sql = 'use '+@Database Print @Sql Execute (@Sql)

Choose * FROM TABLE -- Your query here Finish Close DBName
Deallocate DBName Finish