Let me add my version too. No cursors in the implementation. Many (but not all) of the latest SQL server versions' index options. Both indexes and primary keys are scripted. Plus, it also generates statements to drop the indexes and/or primary keys on the selected tables.
Here it is. Please let me know if you notice any oversights.
with cte as (
select
tbl.object_id,
ix.index_id,
ix.is_primary_key,
x.create_statement,
x.drop_statement
from sys.tables tbl
inner join sys.indexes ix on (ix.object_id = tbl.object_id)
left outer join sys.key_constraints kc on (ix.is_primary_key = 1 and kc.parent_object_id = ix.object_id and kc.unique_index_id = ix.index_id and kc.type = 'PK')
left outer join sys.data_spaces ds on (ds.data_space_id = ix.data_space_id)
left outer join sys.stats st on (st.object_id = ix.object_id and st.stats_id = 1)
cross apply (
select N'create'
+ case ix.is_unique when 1 then ' unique' else '' end
+ case ix.type when 1 then ' clustered' else '' end
+ ' index ' + quotename(ix.name)
+ ' on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ '('
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 0
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')'
+ isnull( ' include (' +
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 1
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')', '')
+ case ix.has_filter when 1
then ' where ' + ix.filter_definition
else ''
end
+ isnull( ' with ('
+ stuff((
select ', ' + w.txt as [text()]
from (
select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0
union all select 'PAD_INDEX = ON' where not ix.is_padded = 0
union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0
--SORT_IN_TEMPDB = ON
union all select 'STATISTICS_NORECOMPUTE = ON' where not isnull(st.no_recompute, 0) = 0
--DROP_EXISTING = ON
--ONLINE = ON
union all select 'ALLOW_ROW_LOCKS = ON' where not ix.allow_row_locks = 0
union all select 'ALLOW_PAGE_LOCKS = ON' where not ix.allow_page_locks = 0
--MAXDOP =
--DATA_COMPRESSION = NONE | ROW | PAGE
) w
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '') + ')'
,''
)
+ isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,
N'drop index ' + quotename(ix.name) + ' '
+ 'on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id)) as drop_statement
where not ix.is_primary_key = 1
union all
select N'alter table '
+ quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ ' add'
+ case kc.is_system_named when 1 then '' else ' constraint ' + quotename(kc.name) end
+ ' primary key '
+ case ix.type when 1 then 'clustered ' else '' end
+ '('
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 0
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')'
+ isnull( ' with ('
+ stuff((
select ', ' + w.txt as [text()]
from (
select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0
union all select 'PAD_INDEX = ON' where not ix.is_padded = 0
union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0
) w
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '') + ')'
,''
)
+ isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,
N'alter table '
+ quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ ' drop constraint ' + quotename(kc.name) as drop_statement
where ix.is_primary_key = 1
) x
where not tbl.is_ms_shipped = 1
and not ix.type = 0
)
select x.stmt + ';
go' as [-- script to drop and recreate all existing primary keys and indexes in the database]
from cte ix
cross apply (
select 1 as stage, ix.drop_statement as stmt
union all select 2 stage, ix.create_statement as stmt
) x
--where ix.object_id = object_id('dbo.Invoices')
order by x.stage,
object_schema_name(ix.object_id),
object_name(ix.object_id),
ix.index_id * case x.stage when 1 then -1 else 1 end
Edit: Fixed an incorrect join to sys.stats. Thanks to ChrisM@Work for notifying me.