question

Mandar Alawani avatar image
Mandar Alawani asked

reindex only non-clustered indexes

Hi All, I have a SQL 2005 SP4 instance... is there a script to rebuild only non-clustered indexes for all tables in a database...plan is to rebuild clustered indexes separately manually.. thanks a lot in advance...
sql-server-2005indexingindexesrebuildnonclustered-index
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Is there a reason you want to maintain the clustered indexes manually? If you could use a more general approach, Ola Hallengren and Michelle Ufford both have very complete and widely used sets of index maintenance scripts here: [ http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html][1] and here: [ http://sqlfool.com/2011/06/index-defrag-script-v4-1/][2] If you want to treat the indexes separately, you could hack into their scripts to only rebuild non-clustered indexes or, more crudely, just use something like the following, using `EXEC(@rebuild)` instead of `print @rebuild`: declare @rebuild varchar(max)= '' select @rebuild = @rebuild + 'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD ' from sys.indexes as i inner join sys.tables as t on i.object_id = t.object_id inner join sys.schemas as s on t.schema_id = s.schema_id where i.type_desc = 'NONCLUSTERED' print @rebuild [1]: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html [2]: http://sqlfool.com/2011/06/index-defrag-script-v4-1/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.