April 14, 2003 at 10:51 am
Hi folks,
I have a job that need to run againt a number of dabatabases. The T-SQL command is identical for all DBs. The only thing different is each time it runs I have to replace the DB name with a predefined DB name inserted into a table. I used a cursor to feed the DB name into a dynamic TSQL command (which is written in a stored proc.) in order to get the expected output. However I try not to use cursor as much as possible so I use another approach. I build a table like this:
ID Database
1 A
2 B
3 C
4 D
.......
Each time the proc runs the database name where ID = 1 is fed into the proc. Next the database name where ID = 2 is fed into the proc, so on until the last one. The second approach seems to use less CPU and less time consumed on the SQL server. Though I am still not sure if my second approach is the only way to get the desired output. I'm just a network admin trying to do server maintenance. I don't know if your folks have any better idea on this? Thx.
April 14, 2003 at 11:36 am
You could use the undocumented procedure "sp_MSForEachDB"...Like this:
exec sp_MSForEachDB 'exec myProc ''?'''
It will replace the "?" with the name of each database and execute the command once for each database. This will give you an idea of what is happening:
exec sp_MSForEachDB 'PRINT ''?'''
-Dan
-Dan
April 14, 2003 at 11:38 am
unfortunately I don't need to run again all DBs, just some of them. Thank you anyway.
April 14, 2003 at 12:00 pm
Then you could still use dj_meier's suggestion. Try this :
EXEC sp_MSForEachDB "IF EXISTS(SELECT * FROM MYDB..MYDBTABLE WHERE dbname = '?') BEGIN exec myProc '?' END"
But it uses a global cursor under the hood.
Tim C //Will code for food
Tim C //Will code for food
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply