Last Updated: February 25, 2016
· johnlbevan

SQL Optimisation :: Convert Primary Keys to Clustered Indexes

--This script is designed for MS SQL Server 
use [insert-db-name-here] 

--disable all constraints on all tables (to avoid these causing errors when altering the indexes) 
sp_msforeachtable 'alter table ? nocheck constraint all' 

declare @sqls table
    object_id bigint, 
    sort int, 
    sql nvarchar(max)

insert @sqls 
select t.objectid
, ic.keyordinal
, case 
when ic.keyordinal=1 then 'CREATE UNIQUE CLUSTERED INDEX [' + + '] ON ' + + ' WITH DROPEXISTING' 
else ']' 
end sql 
from sys.tables t 
inner join sys.indexes i 
on t.objectid = i.objectid 
inner join sys.indexcolumns ic 
on i.objectid=ic.objectid 
and i.indexid = ic.indexid 
inner join sys.columns c 
on ic.objectid = c.objectid 
and ic.columnid = c.columnid 
inner join 
    select objectid
    , indexid
    , MAX(keyordinal) maxko 
    from sys.indexcolumns 
    group by objectid
) icagg 
on i.objectid = icagg.objectid 
and i.indexid = icagg.indexid 
where t.ismsshipped=0 
and i.isprimarykey=1 
and not exists 
( --ignore tables which already have a clustered index 
    select 1 
    from sys.indexes i2 
    where t.objectid = i2.objectid 
    and i2.type = 1 
) order by
, ic.keyordinal

declare @objid bigint 
, @sql nvarchar(max)

while exists 
    select top 1 1 
    from @sqls

    set @sql='' 

    select top 1 @objid=objectid 
    from @sqls 
    select @sql = @sql + sql 
    from @sqls 
    where objectid=@objid 
    order by sort 

    from @sqls 
    where object_id = @objid 

    exec (@sql) 



--reenable constraints to leave the db as we found it (aside from the fix) 
sp_msforeachtable 'alter table ? check constraint all' 

2 Responses
Add your response

Hi John, HTML formatting destroys this script. Can you post a link to an ASCII version?

over 1 year ago ·
over 1 year ago ·