Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get alter index statement from select statement Expand / Collapse
Author
Message
Posted Tuesday, January 7, 2014 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:25 AM
Points: 38, Visits: 172
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 ....
Post #1528468
Posted Tuesday, January 7, 2014 7:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:43 PM
Points: 2,109, Visits: 5,419
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/
Post #1528478
Posted Tuesday, January 7, 2014 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:25 AM
Points: 38, Visits: 172
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
Post #1528481
Posted Tuesday, January 7, 2014 10:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:43 PM
Points: 2,109, Visits: 5,419
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/
Post #1528584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse