How can I do reorg rebuild all indexes in all databases? Can some one help me with script?

Hi rrjegan17,

I need a method or script to run reorg rebuild all tables in all databases.

Hope this helps:

select "reorg rebuild " +name + char(10) +"go" from
sysobjects where type="U"

Copy the results, paste in a new query window and then Run it.

rrjegan17:

I still run into this at some sites. It's well worth you reading the manuals on what exactly dbcc reindex does. It does not (despite the name, and despite what you seem to be suggesting) rebuild indexes in the way that you think.

"dbcc reindex checks the integrity of indexes on user tables by running a fast version of dbcc checktable. For details, see dbcc checktable on page 226. dbcc reindex drops and rebuilds the indexes where the sort order used is not consistent with the new sort order. When dbcc reindex discovers the first index-related error, it displays a message, and then rebuilds the inconsistent indexes. The System Administrator or table owner should run dbcc reindex after changing the sort order in Adaptive Server."

ie if the sort order of the index matches the sort order of the server, nothing happens.


dearvjkumar:

Be aware that you will need the SELECT INTO option to run REORG REBUILD, it will exclusively lock each table as it runs, and running it on one table at a time serially like this is will be slow. Definitely not something to run while users are doing anything with the system.

Can I ask why you want to run this or think you need to? Certainly Sybase ASE tables should be defragmented... maybe 1-4 times/year depending on what you do to them with INSERTs, UPDATEs and DELETEs.

You can use the "optdiag" tool (included for free with ASE) to get an estimate of how fragmented the tables are. Let's put it this way - you certainly don't have to run this weekly or even (probably) monthly.

Hi, thanks for the info. But due to some requirement, i have to enable monthly index rebuild job in our sybase servers. So i m looking for a method to select all database and tables and do reorg rebuild.

But I couldnt find a way to select all database and tables inside each database to execute reorg rebuild.
I need this method not only for reorg rebuild and for someother checking also i required.

So if some has script / proc to execute something on all database and all tables, please help me.

Keeping Joe's warning in mind.

If you increase the points to 500, I will give you a script (two actually).  If you do not have the execute immediate command (12.5.3 IIRC), you will need a shell (not SQL) script; if you have it, the scripts will be entirely SQL (which is much easier).  Try:

Hi Incisive,

I need to loop thru all the DB and all the tables inside each DB. I am looking for a script with that requirement. Can you able to help me?

Yes, I understood, that.  I have such scripts working.  Not just a script/proc that does the <job> on one db.

Note also, along the lines of Joe's post, you have not specified whether you want:

This has not been thought out, or you have some non-technical person giving you orders (which will end in tears for everyone).  I do not do such silly things (as reorg rebuild or update stats) in my existing proc, but I do have a working method which executes at the db_name/table/index level.  You simply supply the command (this is important esp. if you are going to do "someother checking as well").

From the SAG manual:

dearvjkumar,
   Kindly do it in this manner:

1. Tables with heavily fragmented indexes needs to be rebuild
2. Tables with Less fragmented index should be Reorganized.

Just your option to choose either Index Rebuild or Index Reorganize

dearvjkumar:

Building on all the good comments you've received here so far, I strongly suggest you take a step back and think (and discuss with us) what it is you are trying to achieve before getting distracted by what is the correct shell or SQL syntax to implement a particular approach.

You said "But due to some requirement, i have to enable monthly index rebuild job in our sybase servers."

What is the actual requirement?

You said "I need to loop thru all the DB and all the tables inside each DB."

Is that a technical requirement? Are you forced to do it this way? I agree it is very simple to do it this way, but simple is not the same as "best", "suitable" or "appropriate". Do you actually care whether there are iterative loops that single-thread? Have you timed this? What is your maintenance window in which you propose doing this monthly? How much data is in these databases?

Writing a script to do what you want is very simple. A basic "loop through these databases, and loop through the tables (or other objects) in each of those databases, now perform a command per object" is probably the basis of 90% of all DBA scripts ever written.

... but that doesn't mean it's what you should be doing. I and most of the other high-scoring experts here are generally reluctant to tell you how to do something possibly unwise without knowing more about why you think that's what you need to do.

If you say "I have no control or ability to influence this; I have been told to produce a script to do it and that's all that I am interested in" then I suppose we can point you at such a script or show you the outline of one here. I don't think you've told us your Sybase version (or even which Sybase product it is) though. Can you give us at least that much please?

Hi Joe,
Thanks for your detailed advice. Yes, I know the difficulty of enabling reorg / rebuild in PROD system. But I am forced to do it / enable it in a automated way (weekly/monthly). Most of our systems are reporting systems not online systems.

I dont have other choice of enabling it in our environment, thats the reason I am looking for a script to loop thru all database and all tables. This is not only for enabling reorg/rebuild, for other purpose also. (eg. periodical table list / index list, rowcount etc)

Could you help me with the script?

My sybase ASE is 12.5.4 and 15.0.2 under windows and linux as well.

I am not sure if you are dealing with Joe exclusively, and ignoring the other posts due to blindness or lack of courtesy.  I have no comment on Joe's post. I have previously provided considerations you should make, I have stated that I have such  scripts, and I am still waiting for you to answer the question:

IncisiveOne: Just for the record, what you see is what you get, the visible responses here are the only communications I'm involved in or aware of.

If you have a script ready to roll then by all means you should continue here. I've previously done these sorts of iterative loops outside ASE in shell scripts and the like. Your SQL-only solution is more portable and more appropriate when DBA housekeeping has to span multiple platforms.

Summary: Go for it. :)

Comment: Possibly your question is too obscure or arcane for the needs as stated...? (I don't know; I am speculating.) I think we have seen, though, that the needs here are not extremely sophisticated, so the simpler answer (no special handling, ie. reorg rebuild index for the lot) is probably sufficient. (Again, I speculate.)

dearvjkumar: If IncisiveOne puts a script to you, please ensure you select their answer and give them the points. If this were a 500 point question I would ask for 50, but 50/250 is too large a share for what I feel my contribution here has been.

I don't think so.  

For 250 points, you can provide the "very simple" script in SQL, shell, whatever, and you take the points.  I would have thought that, from the outset, it was clear that there is one server involved, so going out to the shell is a bit silly, unless you have no choice.  From 24910694 onwards, it should have been clear that execute immediate was a key issue.  Since OP is on 12.5.4, there is definitely no need to go out to the shell to do something completely within ASE.  I said I had no comment on your post.

I do not want any points for the clarifications and considerations I have provided, if they are beyond the ambit of the seeker.  I will refrain from speculating on your speculations.  However, there is no point in trying provide elegant, efficient scripts to seekers who will not consider the issues; or answer questions (three outstanding); or read my posts.  My script is very short, but it is not simple.  Since you find scripts of this kind "very simple", that's a second prevailing reason.

You guys cannot even keep up with my requirement to change the points to 500, which I specifically declared (I never suggested I would do it for 250).  From the looks of the above, without speculation, the lack of response from the seeker leads me to believe he is not interested in the 500 point script, from his only response being directed at you, it looks like he is proceeding with yours for 250.  Third reason that you  should "go for it".  If this is less than clear to you, read the thread again (sans the interpretations and speculations).

Cheers