SQL Script to run same query on multiple databasa with same table schemas

Question

SQL Script to run same query on multiple databasa with same table schemas

bdba

SSCommitted

Points: 1504
More actions
September 12, 2007 at 11:31 pm

#176588

Hi,
We have servers where we have 20 databases with exact same schemas. We need to frequently run same queries on all these databases. Currently we run it by going into each database and running the query and insert the results into a table on a different database.
I was looking ways where we can create scripts so that it can change databases itself and run the queries on that database one by one.
I am currently trying this by using OSSQL utility by passing it the sql query and getting the database names from the sysdatabase table.
The naming covention for all my 20 databases are pretty similar except for the serial numer in the middle. However, these databases are recreated every month which causes the names to change due to new set of new dates as the suffix.
For example, for the month of September, 2007, they are like this.
Mydb_01_09012007
Mydb_02_09012007
Mydb_03_09012007
Mydb_04_09012007
Mydb_05_09012007

For example, for the month of October, 2007, they are like this.
Mydb_01_10012007
Mydb_02_10012007
Mydb_03_10012007
Mydb_04_10012007
Mydb_05_10012007
Any feedback or good way to handle this would be appreciated. Also,to be able to traverse through six different servers would be great. I am currently doing this by getting the servername by using the function serverproperty('servername') for the current server.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

We have a financial system that uses a database per company and we have about 45 companies at the moment, so I know how you feel with this.

I have a table "Scripts" in a database that was added called DBMaint. The table has an identity column and a VARCHAR(7500) column (the server is SQL 2000). I then have a stored procedure that gets the scripts from this table, loops through Master.dbo.Sysdatabases, and then runs the scripts using Dynamic SQL. The Dynamic SQL looks something like:

SET @sql = 'USE [' + @DB + ']

' + @Script

EXEC(@SQL)

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

I am pretty sure this was in 2000 (although the ".sys." will need changed)

The master.sys.sp_MSforeachdb procedure is good for that. (however, all it does is the USE...) Anywhere you need the DB name put a ?

EXEC

sp_MSforeachdb @command1 = 'select * from ?.sys.database_files'

noeld SSC Guru Points: 96590 More actions · Answer 3

You should either doit from client side code (the loop) or distribute your scripts to the master ( using sp_* ) and running then your stuff locally by looping through sysdatabases.

* Noel

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 4

... or... create a partioned view...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)