For me, this particular code to generate a script to recreate indexes has more practical use in a replication environment.
For example, the default replication setting is to exclude the non-clustered indexes. What if later on you realize some or all of the non-clustered indexes are in-fact needed on subscribers?
Also generally speaking, index needs for publisher vs. subscriber databases can be vastly differently. So you may have different sets of indexes among the publisher and all subscribers.
This code can be executed against pubplisher and/or suscriber databases to generate the index creation script. You can then execute the generated script the subscriber/s databases.
-- If there is duplicated index with different name, it won't catch it
-- If table has Statistics but don't have any index, it will generate "The object does not have any indexes." alert. Please ignroe that if so.
-- Uses features available in SQL server 2005 and up
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if object_id ('tempdb..#ix') > 0 drop table #ix
if object_id ('tempdb..#dba_index') > 0 drop table #dba_index
if object_id('tempdb..#IncludedColumns') is not null drop table#IncludedColumns
create table #ix (tab_name varchar(255),index_name varchar(255),index_description varchar(max),index_keys varchar(max))
create table #dba_index (num int identity(1,1), database_name varchar(128),tab_name varchar(255),index_name varchar(255),index_description varchar(max),index_keys varchar(max), stmt varchar(max))
CREATE TABLE #IncludedColumns (RowNumber smallint, [Name] nvarchar(128))
declare @str varchar(max), @tab varchar(max)
declare @objname varchar(max),@indname varchar(max), @objid int, @indid int
declare @inc_columns nvarchar(max),@inc_Count int, @loop_inc_Count int
-- Going through each table to get indexes.
declare r cursor local fast_forward
for
select distinct so.name
from sysobjects so
join sysindexes si on so.id = si.id
where objectproperty(so.id,'IsUserTable')= 1
and objectproperty(so.id,'IsMSShipped')= 0
and objectproperty(so.id,'IsSystemTable')= 0
and si.indid <> 0 -- exclude the table w/o index
order by so.name asc
open r
fetch next from r into @tab
while @@fetch_status = 0
begin
-- Exclude table with only Statistics exists.
IF EXISTS (SELECT * FROM sysindexes
WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 0 and object_name(id) = @tab)
BEGIN
insert #ix(index_name,index_description,index_keys)
exec sp_helpindex @tab
insert#dba_index (database_name,tab_name,index_name,index_description,index_keys, stmt)
select db_name(),@tab,index_name,index_description,index_keys,
'CREATE ' + case when (index_description like '%UNIQUE%') then 'UNIQUE ' else '' end
+ case when (index_description like '%clustered%' andindex_description not like '%nonclustered%') then 'CLUSTERED' else 'NONCLUSTERED' end
+ ' INDEX [' + index_name + '] ON [' + @tab + ']('+ cast(index_keys as varchar(512)) +')'
from#ix
whereindex_description not like '%primary key%'
truncate table#ix
END
fetch next from r into @tab
end
close r
deallocate r
-- ADD INCLUDED COLUMNS
CREATE INDEX #IDX_dba_index_Tab_Name_Index_Name on#dba_index(tab_name, index_name)
declare c1 cursor for select object_name(object_id), object_id, name,index_id from sys.indexes where object_name(object_id) in
(select distincttab_name from #dba_index) order by object_id,index_id
open c1
fetch c1 into @objname, @objid, @indname, @indid
while @@fetch_status = 0
begin
DELETE FROM #IncludedColumns
insert into #IncludedColumns
SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id) , clmns.name
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS si ON (si.index_id > 0 andsi.is_hypothetical = 0) AND (si.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 oric.partition_ordinal = 0 oric.is_included_column != 0))
AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1
and(si.index_id = @indid) and (tbl.object_id= @objid)
ORDER BY 1
SELECT @inc_Count = count(*) FROM#IncludedColumns
if @inc_Count > 0
begin
SELECT @inc_columns = '[' + [Name] + ']' FROM#IncludedColumns WHERE RowNumber = 1
SET @loop_inc_Count = 1
WHILE @loop_inc_Count < @inc_Count
BEGIN
SELECT @inc_columns = @inc_columns + ', [' + [Name] + ']' FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
SET @loop_inc_Count = @loop_inc_Count + 1
END
set @inc_columns = 'INCLUDE (' + @inc_columns + ')'
-- print @inc_columns
update #dba_index set stmt = stmt + char(13) +@inc_columns where tab_name = object_name(@objid) andindex_name = @indname
end
fetch c1 into @objname, @objid, @indname, @indid
end
close c1
deallocate c1
select
'IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(' + ''''
+ tab_name + '''' + ') AND name = ' + '''' +index_name + '''' + ')' + char(13) + stmt + char(13)
from #dba_index
order by tab_name asc,
index_description asc -- this is needed in order to put clustered index first before nonclustered index is created