running one script against several databases..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

running one script against several databases..

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2001-12-19 : 14:42:41

Hello,

Is there an easy way to run one script against several databases using enterprise manager? For example, I have to take one script and run it against 8 different databases. Instead of running it, changing the db, running it, changing the db, running it...etc, can I specify the databases and have it run against all of them?

Thanks,
Eddie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 14:47:52

There is an undocumented system procedure called "sp_MSforeachdb", which is documented in....wait for it....

The Guru's Guide to Transact-SQL!

You can use it like this:

sp_MSforeachdb @command1='BACKUP LOG ? TO BACKUP1'

The "?" indicates the database name; each database on the server will be processed and its name placed where the "?" appears in the command text.

You can also do this with dynamic SQL:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

It might be a little more work at first but you can do more intricate things with it.

Edited by - robvolk on 12/19/2001 14:50:29

mfemenel
Professor Frink

1421 Posts

Posted - 2001-12-19 : 14:54:22

or you can write it this way if you don't want to use each database, as you may have others it's inappropriate for.

Use databasename
go
sql statement here

use databasename
go
sql statement here

Mike
"A program is a device used to convert data into error messages."

eddie
Starting Member

45 Posts

Posted - 2001-12-19 : 15:00:39

can I call a script from within enterprise manager? Example

use databasename
go
execute
c:\folder\scriptname

Becuase if I had to past the script in each time it would defeat the purpose.

Thanks,
Eddie

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-19 : 15:06:31

You could use isql or osql to run a script file. Possibly lump calls to osql in a batch file.

-------------------
It's a SQL thing...

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-19 : 15:07:23

Look at the osql utility.
This allows you to run a script in a text file and specify the database to run it against.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources