How to execute T-SQL for several databases whose names are stored in a table
- by emzero
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!
