--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