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