All Databases > All tables > DBCC SHOWCONTIG
-
I run a "script" that grabs every database on a sql 2005 server, "cursors" through every table in each database and runs DBCC SHOWCONTIG on each table. I use cursors and a VB program (to get the database names). Works but seems kliudgy to me. I would like to know how the "sophisticated" DBAs out there handle this matter (i.e. looping through all the databases, then each table in each databse.)
TIA,
Bill
-
If you do a search on DBCC SHOWCONTIG on this site you will find several scripts that do a SHOWCONTIG. Pick the one that best suits your needs. To execute the script in multiple databases you something like:
EXEC sp_MSforeachdb "USE ? IF db_name() NOT IN ('master', 'tempdb', 'model', 'msdb')
EXEC sp_indexdefragLargeTables 80000, 90 "
Where the EXEC statement executes the proc of your choice. This gets rid of VB. Whether the proc uses cursors or not is your choice. You can use this http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=97 to avoid cursors but then you may still be directly accessing a system table so you may want to try using the INFORMATION_SCHEMA.TABLES view instead.
Francis
-
Aye, sp_MSforeachdb is often used but it also uses cursors.
So if you use it, you're still using cursors, it just looks cleaner.K. Brian Kelley
@kbriankelley -
Thanks for the help. I have never used sp_MSforeachdb before. As a learning experience I tried this statement
EXEC sp_MSforeachdb "USE ? IF db_name() NOT IN ('master', 'tempdb', 'model', 'msdb') print '?', DBCC CHECKDB(?) "
I learned that I kept getting the error message "Line 1: Incorrect syntax near ','." What am I doing wrong?
TIA,
Bill
-
Use a ; not a , as a command separator
Francis
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply