Generate Index only scripts for whole DB

  • Is there a way to generate only the indexes for an entire DB. If I right click the DB and go to tasks>Generate scripts I can check create indexes but I always get the tables. The reason I'm needing only the indexes is we are migrating from Informix DB to SQL Server and we are using SSIS packages. With a fast load in the package the data transfer is extremely fast with no indexes but slower when indexes exists are the target tables. So after the data move we want to then apply all the required indexes.

    Plus our project is in steps, so we are adding new tables daily and it's easier to create the complete script to build our target database using this generate scripts. But it would be a tedious task if we have to create it with indexes and then manually remove the table creation portion to get the index script.

    Thanks

    Joe

  • http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

    was written for sql 2005 but probably a decent place to start.

  • i have this "SCRIPT ALL INDEXES with Include" saved in my snippets, and supposedly it will also do the INCLUDE syntax, if it was used. I haven't fully tested it yet.

    see if this helps you out:

    --1. get all indexes from current db, place in temp table

    select

    tablename = object_name(i.id),

    tableid = i.id,

    indexid = i.indid,

    indexname = i.name,

    i.status,

    isunique = indexproperty (i.id,i.name,'isunique'),

    isclustered = indexproperty (i.id,i.name,'isclustered'),

    indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')

    into #tmp_indexes

    from sysindexes i

    where i.indid > 0 and i.indid < 255 --not certain about this

    and (i.status & 64) = 0 --existing indexes

    --add additional columns to store include and key column lists

    alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)

    go

    --################################################################################################

    --2. loop through tables, put include and index columns into variables

    declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

    declare index_cursor cursor for

    select tableid, indexid from #tmp_indexes

    open index_cursor

    fetch next from index_cursor into @tableid, @indexid

    while @@fetch_status <> -1

    begin

    select @isql_key = '', @isql_incl = ''

    select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *

    --key column

    @isql_key = case ic.is_included_column

    when 0 then

    case ic.is_descending_key

    when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '

    else @isql_key + coalesce(sc.name,'') + ' ASC, '

    end

    else @isql_key end,

    --include column

    @isql_incl = case ic.is_included_column

    when 1 then

    case ic.is_descending_key

    when 1 then @isql_incl + coalesce(sc.name,'') + ', '

    else @isql_incl + coalesce(sc.name,'') + ', '

    end

    else @isql_incl end

    from sysindexes i

    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(i.indid AS int) AND ic.object_id=i.id)

    INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id

    where i.indid > 0 and i.indid < 255

    and (i.status & 64) = 0

    and i.id = @tableid and i.indid = @indexid

    order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

    if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)

    if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)

    update #tmp_indexes

    set keycolumns = @isql_key,

    includes = @isql_incl

    where tableid = @tableid and indexid = @indexid

    fetch next from index_cursor into @tableid,@indexid

    end

    close index_cursor

    deallocate index_cursor

    --remove invalid indexes,ie ones without key columns

    delete from #tmp_indexes where keycolumns = ''

    --################################################################################################

    --3. output the index creation scripts

    set nocount on

    --separator

    select '---------------------------------------------------------------------'

    --create index scripts (for backup)

    SELECT

    'CREATE '

    + CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END

    + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END

    + 'INDEX [' + INDEXNAME + ']'

    +' ON [' + TABLENAME + '] '

    + '(' + keycolumns + ')'

    + CASE

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'

    WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'

    ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'

    END

    FROM #tmp_indexes

    where left(tablename,3) not in ('sys', 'dt_') --exclude system tables

    order by tablename, indexid, indexname

    set nocount off

    --drop table #tmp_indexes

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • See my post at the bottom of this page

    http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx

    The script generates index statements and incorporates table schemas, file groups, drop statements and fill factor.

  • Sorry to bring up an old post but I thought I'd add to the enclosed script by including schema names...

    --1. get all indexes from current db, place in temp table

    select

    schemaName = s.name,

    tablename = object_name(i.id),

    tableid = i.id,

    indexid = i.indid,

    indexname = i.name,

    i.status,

    isunique = indexproperty (i.id,i.name,'isunique'),

    isclustered = indexproperty (i.id,i.name,'isclustered'),

    indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')

    into #tmp_indexes

    from sysindexes i

    INNER JOINsys.tables t ON i.id = t.object_id

    INNER JOINsys.schemas s ON t.schema_id = s.schema_id

    where i.indid > 0 and i.indid < 255 --not certain about this

    and (i.status & 64) = 0 --existing indexes

    --add additional columns to store include and key column lists

    alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)

    go

    --################################################################################################

    --2. loop through tables, put include and index columns into variables

    declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

    declare index_cursor cursor for

    select tableid, indexid from #tmp_indexes

    open index_cursor

    fetch next from index_cursor into @tableid, @indexid

    while @@fetch_status <> -1

    begin

    select @isql_key = '', @isql_incl = ''

    select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *

    --key column

    @isql_key = case ic.is_included_column

    when 0 then

    case ic.is_descending_key

    when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '

    else @isql_key + coalesce(sc.name,'') + ' ASC, '

    end

    else @isql_key end,

    --include column

    @isql_incl = case ic.is_included_column

    when 1 then

    case ic.is_descending_key

    when 1 then @isql_incl + coalesce(sc.name,'') + ', '

    else @isql_incl + coalesce(sc.name,'') + ', '

    end

    else @isql_incl end

    from sysindexes i

    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(i.indid AS int) AND ic.object_id=i.id)

    INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id

    where i.indid > 0 and i.indid < 255

    and (i.status & 64) = 0

    and i.id = @tableid and i.indid = @indexid

    order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

    if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)

    if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)

    update #tmp_indexes

    set keycolumns = @isql_key,

    includes = @isql_incl

    where tableid = @tableid and indexid = @indexid

    fetch next from index_cursor into @tableid,@indexid

    end

    close index_cursor

    deallocate index_cursor

    --remove invalid indexes,ie ones without key columns

    delete from #tmp_indexes where keycolumns = ''

    --################################################################################################

    --3. output the index creation scripts

    set nocount on

    --separator

    select '---------------------------------------------------------------------'

    --create index scripts (for backup)

    SELECT

    'CREATE '

    + CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END

    + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END

    + 'INDEX [' + INDEXNAME + ']'

    +' ON [' + schemaName + '].[' + TABLENAME + '] '

    + '(' + keycolumns + ')'

    + CASE

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'

    WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'

    WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'

    ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'

    END

    FROM #tmp_indexes

    where left(tablename,3) not in ('sys', 'dt_') --exclude system tables

    order by schemaName, tablename, indexid, indexname

    set nocount off

    drop table #tmp_indexes

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply