I was looking for an alternative solution to using the maintenance plans in SQL 2000 and found an article here on SSP that is perfect for my maintenance needs.
http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p2.aspx
It is very similar the BOL reference to DBCC SHOWCONTIG with a few needed tweaks. However, I'm having problems executing as is according to the article by Tom Pullen. I've seen him around on the forums, so maybe he'll dive in, but basically I'm getting an error try to create the SP.
Msg 207, Level 16, State 3, Procedure sp_defragment_indexes, Line 121
Invalid column name '''.
Msg 207, Level 16, State 3, Procedure sp_defragment_indexes, Line 121
Invalid column name '''.
Here is the line in question:
SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
Unfortunately, I'm an involuntary DBA, so I don't have the sharpest syntax skills, but I played around with the concatenation to a point where I could execute the SP. Nonetheless, I failed, as the results of actually passing the parameter results in an error over and over (for each index falling within the parameter).
Line 1: Incorrect syntax near '.'.
Msg 170, Level 15, State 1, Line 1
Could anyone guide me to what I'm missing? Thanks in advance!
dF