How to execute T-SQL for several databases whose names are stored in a table

Posted by emzero on Stack Overflow See other posts from Stack Overflow or by emzero
Published on 2010-03-17T19:06:28Z Indexed on 2010/03/17 19:21 UTC
Read the original article Hit count: 410

Hey guys, so here's the deal.

I have several databases (SqlServer 2005) on the same server with the same schema but different data.

I have one extra database which has one table storing the names of the mentioned databases.

So what I need to do is to iterate over those databases name and actually "switch" to each one (use [dbname]) and execute a T-SQL script. Am I clear?

Let me give you an example (simplified from the real one):

CREATE TABLE DatabaseNames
(
   Id   int,
   Name varchar(50)
)
INSERT INTO DatabaseNames SELECT 'DatabaseA'
INSERT INTO DatabaseNames SELECT 'DatabaseB'
INSERT INTO DatabaseNames SELECT 'DatabaseC'

Assume that DatabaseA, DatabaseB and DatabaseC are real existing databases. So let's say I need to create a new SP on those DBs. I need some script that loops over those databases and executes the T-SQL script I specify (maybe stored on a varchar variable or wherever).

Any ideas?

Thanks!

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about iteration