September 12, 2006 at 10:57 am
Hi. I am not a programmer and fairly new to SQL scripting. Bear with me.
I'd like to set up a job that selectively reindexes the tables in my databases based on a user-defineable percent of fragementation. I have obtained that code from a page that is located on this very site and it works nicely.
Now I would like to set up a SQL Job that will run this sp, on a schedule, against all the databases on my server.
The code already has a built-in method to avoid running on system databases.
So, instead of setting up my job like so:
use Database1
go
EXEC sp_defragment_indexes
go
use Database2
go
EXEC sp_defragment_indexes
go
..which would not automatically add any newly created dbs, I would like to set something up like this (my code, of course, is not correct syntax but my own sort of pseudocode to give you an idea of what I want)
Define Variable
dbname=name.sysdatabases
BEGIN
While dbname != NULL
use dbname
go
EXEC sp_defragment_indexes
go
END
If anyone could assist me in the proper syntax for the code, if indeed this is possible, please let me know. Thanks.
September 12, 2006 at 1:48 pm
Have a look at the sp_MSforeachdb undocumented MS stored Procedure. You can read about it by searching on this site or reading this...
http://www.databasejournal.com/features/mssql/article.php/3441031
September 13, 2006 at 6:24 am
Alternatively you could use dynamic SQL to set the DB and also execute the SP. You'd need to exec the SP in the same context as the DB you've specified because doing something like
USE DB1
EXEC('USE DB2')
EXEC sp_An_SP_in_DB2
won't work because the DB engine will look for the sp in DB1.
Instead try
EXEC('USE DB2; EXEC sp_An_SP_in_DB2')
However, if the SP you want to execute is not necessarily in all DBs you'll need to reference the DB that it is in (e.g. DB1) in your dynamic SQL too. So:
EXEC('USE DB2; EXEC DB1..sp_An_SP_in_DB2').
Hope this helps.
September 13, 2006 at 7:04 am
Yes but in that case you'd probably need to set up some sort of cursor to loop over each database if you want this all executed in one job, and that keeps adding larger degrees of complexity and allows more room for problems to occur. What about when a new database is added? Does he now manually have to change his code? Particularly if Private gripweed is as he says he is "not a programmer and fairly new to SQL scripting". The foreachdb method is easy to use and takes all of the guesswork out of it.
Gripweed as a point of reference where is your sp_defragment_indexes currently located? Remember that when you have a stored Procedure located in any database other than Master it is recommended that you don't prefix it with sp_ because the Database engine will search master for it first before it drops to the current database to find your procedure. This will result in a bit of a performance hit and as a rule should be avoided.
September 13, 2006 at 3:20 pm
Declare @db varchar(50)
Declare @id int
I use the following code to make backups of all relevant databases (including any newly created databases) except for the system databases and those that are Mirror partners.
Declare @cmd varchar(800)
Declare curDB CURSOR
FOR
Select name, database_id from Master.sys.databases
where Database_id >6 and Database_id Not In(
Select Database_id from msdb.sys.database_mirroring
where mirroring_role_desc='Mirror')
Open curDB
Fetch Next from curDB into @db, @id
While @@Fetch_status=0
BEGIN
Set @cmd='Backup database ' + @db + ' to disk=' + char(39) + 'e:\backup\' + @db + '_DB.BAK' + Char(39) + 'with init'
exec(@cmd)
Fetch Next from curDB into @db, @id
END
close curDB
deallocate curDB
This is an instance where using a cursor doesn't carry any significant penalty.
HTH,
Elliott
September 20, 2006 at 7:50 am
I used sp_MSforeachdb. Good one....thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply