Post reply

Script to create all clustered indexes

  • January 25, 2011 at 2:07 am

    #388710

    I am troubleshooting a problem and need to create a script to drop all clustered indexes and then recreate them. Yes, I could use ALTER TABLE or CREATE INDEX with DROP_EXISTING but I need to do it this way for troubleshooting reasons.

    I have the following query

    SELECT

    indexes.name as indexName,

    schemas.name as schemaName,

    objects.name as objectName,

    columns.name as columnName

    FROM sys.indexes indexes

    JOIN sys.index_columns ic on indexes.OBJECT_ID = ic.object_id and indexes.index_id = ic.index_id

    JOIN sys.columns columns on ic.object_id = columns.object_id and ic.column_id = columns.column_id

    JOIN sys.objects objects ON indexes.OBJECT_ID = objects.OBJECT_ID

    JOIN sys.schemas schemas ON objects.schema_id = schemas.schema_id

    WHERE indexes.index_id > 0

    AND indexes.index_id < 255

    AND objects.is_ms_shipped = 0

    and indexes.type_desc = 'CLUSTERED'

    ORDER BY objects.name, ic.key_ordinal

    I need to have a column in the SELECT to do CREATE CLUSTERED INDEX indexName on objectName(columns...)

    I need to aggregate the columns somehow - any ideas on how to do this?

  • January 25, 2011 at 3:03 am

    #1277685

    Please ignore this, I hadn't realised that a logical definition of a constraint is bound to the physical manifestation (an index). It seems that dropping and recreating all indexes is not a trivial thing.

Viewing 2 posts - 1 through 1 (of 1 total)

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