How do I automate converting heaps into clustered indexes?
I would say that I agree with @MartinSmith. Determining the clustered index key is something that requires some thought and planning.
But if you were looking to get a head start and generate some T-SQL code that, say, creates the CREATE INDEX
commands on heaps that have an IDENTITY
column (a generally accepted clustered index key), then you could do something like this:
declare @create_indexes nvarchar(max);
set @create_indexes = N'';
select
@create_indexes = @create_indexes +
'create clustered index ' + quotename('IX_' + object_name(i.object_id)) +
char(13) + char(10) +
'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
char(13) + char(10) +
'go' +
char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;
print @create_indexes;
Again, I will restate that like @MartinSmith said, I wouldn't just blindly execute any DDL that will impact performance and design like this. But the above is a start, and will give you the T-SQL for heaps that have an IDENTITY column.