Generate script to recreate indexes

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%' and index_description not like  '%nonclustered%') then 'CLUSTERED' else 'NONCLUSTERED' end 

            + ' INDEX [' + index_name + '] ON [' + @tab + ']('+ cast(index_keys as varchar(512)) +')' 

     

        from #ix   

        where index_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 distinct tab_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 and si.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 or ic.partition_ordinal = 0 or ic.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) and index_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

---