Geeks With Blogs

News
The Wrecking Bawl Destructuring query language, one keyword at a time.
I have a real-time reporting server with a database which is basically a copy of my production database, but it's populated using transactional replication and replication only copies the primary keys over, i.e. none of the secondary indexes are included.  So what I do is every time I need to recreate replication--which is every deployment with schema changes--I script out DROPs and CREATEs for all the reporting indexes I've created and then run the script after replication has been recreated.  The script I use is a modified conglomeration of scripts I've found on the Internet, but you may find it useful.  Note that it excludes primary keys and some other things.


PRINT '-------------------------  DROP INDEXES     ----------------------------------------------------'
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR
    SELECT indexes.name, objects.name, schemas.name
    FROM sys.indexes
    JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
    JOIN sys.schemas ON objects.schema_id = schemas.schema_id
    WHERE indexes.index_id > 0
      AND indexes.index_id < 255
      AND objects.is_ms_shipped = 0
      AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
    ORDER BY indexes.name


--SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'IF EXISTS (SELECT 1 FROM sys.indexes i WHERE i.name = ''' + @indexname +  ''') DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql 
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes


PRINT CHAR(10) + CHAR(13) + '-------------------------  CREATE INDEXES     ----------------------------------------------------'


declare
@object_id          int,
@index_id           tinyint,
@schema_name        sysname,
@table_name         sysname,
@index_name         sysname,
@type               tinyint,
@uniqueness         bit,
@indexed_column     sysname,
@included_column    sysname,
@indexed_columns    varchar(max),
@included_columns   varchar(max),
@has_included_cols  bit,
@is_descending_key  bit,
@stmt               varchar(max),
@crlf               char(2)

set @crlf = char(13) + char(10)

declare indexes cursor
for
    select
    schema_name     = s.name,
    table_name      = t.name,
    index_id        = i.index_id,
    index_name      = i.name,
    type            = i.type,
    uniqueness      = i.is_unique
from
         sys.schemas            s
    join sys.tables             t   on s.schema_id = t.schema_id
    join sys.indexes            i   on t.object_id = i.object_id
where
    i.type > 0  -- none -heap
    AND I.index_id > 0 -- ignore PKs
    AND I.index_id < 255
    AND i.type = 2
    AND INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') =0
    AND left(I.object_id,3) not in ('sys', 'dt_')
    AND left(I.name,3) not in ('PK_', 'UQ_','nci','ucm','ci_','uk_')
order
    by i.name

open indexes

fetch
    indexes
into
    @schema_name,
    @table_name ,
    @index_id   ,
    @index_name ,
    @type       ,
    @uniqueness

while @@fetch_status<>(-1)
begin

    select @object_id = object_id(@schema_name + '.' + @table_name)
    set @indexed_columns = '('

    declare indexed_columns cursor
    for
        select
            c.name,
            ic.is_descending_key
        from
                 sys.index_columns  ic
            join sys.columns        c   on ic.column_id = c.column_id
                                       and ic.object_id = c.object_id
        where
            ic.object_id = @object_id
            and ic.index_id = @index_id
            and ic.is_included_column = 0
        order by
            ic.index_column_id

    open indexed_columns

    fetch indexed_columns
    into @indexed_column, @is_descending_key

    while @@fetch_status<>(-1)
    begin

        set @indexed_columns = @indexed_columns + @indexed_column +
                               case @is_descending_key when 1 then ' DESC ' else '' end + ', '

        fetch indexed_columns
        into @indexed_column, @is_descending_key

    end

    close indexed_columns
    deallocate indexed_columns

    set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'

    if exists
           (select  object_id
            from    sys.index_columns
            where   object_id = @object_id
                    and index_id = @index_id
                    and is_included_column = 1 )
    begin
        set @included_columns = 'INCLUDE ('

        declare included_columns cursor
        for
        select
            c.name,
            ic.is_descending_key
        from
                 sys.index_columns  ic
            join sys.columns        c   on ic.column_id = c.column_id
                                       and ic.object_id = c.object_id
        where
            ic.object_id = @object_id
            and ic.index_id = @index_id
            and ic.is_included_column = 1
        order by
            ic.index_column_id

        open included_columns

        fetch included_columns
        into @included_column, @is_descending_key

        while @@fetch_status<>(-1)
        begin

            set @included_columns = @included_columns + @included_column +
                                    case @is_descending_key when 1 then ' DESC ' else '' end + ', '

            fetch included_columns
            into @included_column, @is_descending_key

        end

        close included_columns
        deallocate included_columns

        set @included_columns = left(@included_columns, len(@included_columns)-1) + ') '

    end

    set @stmt =
    'CREATE ' +  case @uniqueness when 1 then 'UNIQUE ' else '       ' end + case @type when 1 then 'CLUSTERED ' else '' end +
    'INDEX ' + @index_name + ' ON ' + @schema_name + '.' + @table_name + @indexed_columns + ' ' + isnull(@included_columns,'') + @crlf
   
    set @included_columns = ''

    print @stmt

    fetch
        indexes
    into
        @schema_name,
        @table_name ,
        @index_id   ,
        @index_name ,
        @type       ,
        @uniqueness

end

close indexes
deallocate indexes

Posted on Wednesday, January 4, 2012 9:22 AM all tech stuff , SQL Server | Back to top


Comments on this post: drop and recreate sql server indexes

# re: drop and recreate sql server indexes
Requesting Gravatar...
Thanks for the code, I will test it out
Left by nadja on Nov 07, 2012 12:24 AM

# re: drop and recreate sql server indexes
Requesting Gravatar...
Thanks a lot,I currently work on a SQL project and got stuck with the indexes part. This should help me to get going again...
Left by Maria on Nov 30, 2012 8:19 AM

Your comment:
 (will show your gravatar)


Copyright © Alex Bransky | Powered by: GeeksWithBlogs.net