Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command:

ALTER INDEX all ON db REBUILD

And I get the following error:

Cannot find the object "db" because it does not exist or you do not have permissions

What am I doing wrong?

share|improve this question
    
Does the user that you are acting as have the correct access to the database? Are you able to create a maintenance plan that rebuilds the indexes, and execute that? – RateControl Apr 30 '11 at 4:02
up vote 9 down vote accepted

To do all tables:

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

There is no statement to do all tables in the database like you tried above

However, sometimes you don't want this, say for read only tables or huge tables. Also, some tables/indexes will hardly be fragmented.

A more intelligent way is to check fragmentation first then rebuild or reorganise. One common and widely used example is SQL Fool's script

share|improve this answer
    
Another one commonly used by DBAs is Ola Hallengeren's scripts – sqld-_-ba Jun 23 '15 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.