How to get alter index statement from select statement

Question

How to get alter index statement from select statement

Akayisi

Right there with Babe

Points: 735
More actions
January 7, 2014 at 7:01 am

#286935

Hi guys i want to get alter index statement from my select
In order to do thAT I have this,
select 'ALTER INDEX '+name+ ' ON'+' rebuild' from sys.indexes
where object_id = (select object_id from sys.objects where name = 'TABLE_NAME')
and
name is not null
the output is
ALTER INDEX index_name ON rebuild
IT SHOULD BE;
ALTER INDEX index_name ON TABLE_NAME rebuild
I'm missing the table name part. I tried to use INFORMATION_SCHEMA.columns.TABLE_NAME but it did not work since it produces 3 index statements (because i have three tables). Any suggestions ?
Thanks in advance ....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Here is one way of doing it:

select 'alter index [' + si.name + '] on [' + schema_name(so.schema_id) + '].[' + so.name + '] rebuild with (online = on)'

from sys.indexes si inner join sys.objects so on si.object_id = so.object_id

where si.object_id = object_id('TableName')

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Akayisi Right there with Babe Points: 735 More actions · Answer 2

Thanks a lot by the way i guess this is also working

select 'ALTER INDEX '+sys.indexes.name+ ' ON '+sys.tables.name+' rebuild' from sys.indexes,sys.tables

where sys.indexes.object_id=sys.tables.object_id

and

sys.indexes.name is not null

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Akayisi (1/7/2014)
Thanks a lot by the way i guess this is also working
select 'ALTER INDEX '+sys.indexes.name+ ' ON '+sys.tables.name+' rebuild' from sys.indexes,sys.tables
where sys.indexes.object_id=sys.tables.object_id
and
sys.indexes.name is not null

This can work as long as you don't have more then 1 schema. Also this will create a script for all indexes on all the tables and not just one table as you asked in the original post.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/