March 18, 2009 at 12:47 pm
I would like to run a script that will analyze the indexes (and potentially another script for stats) on a bunch of user databases, but have this script automated. If it was only 10-12 databases, I don't see it being too big of a deal, but it's close to 40.
Is there a good way about doing this? I would like to run the MPlans to do the Index tasks, but I really don't want to rebuild every index if it doesn't need it, therefore making my script a little more specialized.
Would i need to create a SSIS package and just add every database connection and related execute SQL Task to run the script?
Or would there be a way to pass the database name into my script to then use? Unfortunately, the USE statement isn't something that can be passed to through a proc or function.
Any suggestions will be much appreciated! Thanks
March 18, 2009 at 6:12 pm
How about using the batching jobs with SQLCMD Variables?
Check out .. http://msdn.microsoft.com/en-us/library/ms188714.aspx
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 12:25 am
Just an idea. Don't know if it would work but worth a shot.
Take your script and create it as a stored procedure in Master with SP_Name. You should be able to access it from anywhere then.
Then use the sp_MSForEachDB "Exec ?..SP_Name"
I have done something similar to this for reindexing tables. Will see if I can dig up the script, it went through and everything with a fragmentation of X it reindexed the table.
That would probably be my first idea.
Good luck, let us know the solution
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply