SQL Optimisation :: Convert Primary Keys to Clustered Indexes

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

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

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 [' + i.name + '] ON ' + t.name + ' 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
    ,indexid 
) 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 t.name
, i.name
, ic.keyordinal

declare @objid bigint 
, @sql nvarchar(max)

while exists 
(
    select top 1 1 
    from @sqls
) 
begin 

    set @sql='' 

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

    delete 
    from @sqls 
    where object_id = @objid 

    exec (@sql) 

end

go

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