Script all indexes as CREATE INDEX statements

  • Lowell

    SSC Guru

    Points: 323450

    Comments posted to this topic are about the item Script all indexes as CREATE INDEX statements

    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!

  • Vishal Sinha

    Ten Centuries

    Points: 1174

    this is a great script. But it does not work if there are Included columns. Example my index looks like this:

    CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName

    (

    Column1

    , Column2

    )

    INCLUDE

    (

    Column3

    , Column4

    , Column5

    )

    But your script creates it as follows:

    CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName

    (

    Column1

    , Column2

    , Column3

    , Column4

    , Column5

    )

    Any clues on how to modify your script to get what I need?

    Thanks!

  • Pravin Patil

    Valued Member

    Points: 67

    Res Sir,

    Thanks for this script.

    I have i problem that it will not run for sql server 2005 databases having

    compatibility level 90.

    Kindly request you to send the script which will run on sql server 2005 databases having

    compatibility level 90.

    Thanking you in advance.

  • DBA-640728

    SSChampion

    Points: 12896

    did you find a way to get the included columns?

  • anatshapira8

    Valued Member

    Points: 74

    ORDER BY SYSCOLUMNS.COLID should be ORDER BY sysindexkeys.keyno

    Ordering by COlID causes wrong columns order in the key.

  • Lowell

    SSC Guru

    Points: 323450

    i contributed that script a while ago, and clearly it's only for SQL 2000;

    here's someone elses script that I saved in my snippets that does INCLUDE columns;it does PRINT statements instead of a SELECT, but that's easy to modify; it alos includes the owner/schema, where my original was just assuming dbo.

    I'll modify my script to do the same thing and post it after i test it a little bit.

    --------------------------------------------------------------------

    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

    order

    by s.name,

    t.name,

    i.index_id

    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) + ')' + @crlf

    end

    set @stmt =

    'create ' +

    case @uniqueness when 1 then 'unique ' else '' end +

    case @type when 1 then 'clustered ' else '' end +

    'index ' + @index_name + @crlf +

    'on ' + @schema_name + '.' + @table_name + @indexed_columns + @crlf +

    isnull(@included_columns,'') +

    'g' + 'o' + @crlf + @crlf

    print @stmt

    fetch

    indexes

    into

    @schema_name,

    @table_name ,

    @index_id ,

    @index_name ,

    @type ,

    @uniqueness

    end

    close indexes

    deallocate 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!

  • thorv-918308

    SSC Veteran

    Points: 229

    Hi

    Thanks for the original query!

    I modified it to have includes.

    Additionally will create composite index columns in the correct sequence, I found that your original query worked well, however the sequence that composite indexes added the columns in was incorrect.

    e.g.

    existing index would look like this ->

    CREATE INDEX ix_123 on

    ([COL1], [COL2], [COL3][)

    however the output would look like this ->

    CREATE INDEX ix_123 on

    ([COL2], [COL1], [COL3])

    --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 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

  • thorv-918308

    SSC Veteran

    Points: 229

    Ah additionally I have added fill factor, and a really cool addition- with ONLINE = ON for nonclustered indexes (so you don't lock the production table when adding the index).

    Note: ONLINE = ON doesn't work for clustered indexes.

  • alaine.warfield

    Valued Member

    Points: 63

    Thank you very much for posting these scripts.

    I had scripts for SQL 2000 but nothing that handled include columns

    This is really appreciated!

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks Lowell and Thorv.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • mfuller333

    SSC Enthusiast

    Points: 130

    Nice script...

    Added back the ability to include or exclude PK and Unique indexes, as well as schema if your not using the default dbo. Also now creates the drop statement as well.

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

    select

    tablename = schemas.name + '].[' + 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

    JOIN sys.objects ON i.id = objects.object_id

    JOIN sys.schemas ON objects.schema_id = schemas.schema_id

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

    and (i.status & 64) = 0

    AND INDEXPROPERTY (i.id,i.name,'ISUNIQUE') =0 --comment out to include unique and

    AND INDEXPROPERTY (i.id,i.name,'ISCLUSTERED') =0 --include PK's

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

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

    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)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_') --exclude system tables

    order by tablename, indexid, indexname COLLATE Latin1_General_BIN2

    set nocount off

    --drop table #tmp_indexes

    --makes the drop

    SELECT 'DROP INDEX '

    +' [' + tablename + '].['

    + indexname + ']'

    FROM #tmp_indexes where left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_')

  • Sander A.

    Default port

    Points: 1440

    I made a little change to the last script to also include the where clause of a filtered index. I also removed the collate because this didn't work for me.

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

    select

    tablename = schemas.name + '].[' + object_name(i.object_id),

    tableid = i.object_id,

    indexid = i.index_id,

    indexname = i.name,

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

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

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

    CASE WHEN i.filter_definition IS NULL THEN '' ELSE ' WHERE '+i.filter_definition END Filter_Definition

    into #tmp_indexes

    from sys.indexes i

    JOIN sys.objects ON i.object_id = objects.object_id

    JOIN sys.schemas ON objects.schema_id = schemas.schema_id

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

    --AND INDEXPROPERTY (i.object_id,i.name,'ISUNIQUE') = 0 --comment out to include unique and

    AND INDEXPROPERTY (i.object_id,i.name,'ISCLUSTERED') =0 --include PK's

    --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 Filter_Definition

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

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

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

    ELSE ' INCLUDE(' + INCLUDES + ') '+Filter_Definition+' 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

    --makes the drop

    SELECT 'DROP INDEX '

    +' [' + tablename + '].['

    + indexname + ']'

    FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_')

    --Drop the temp table again

    DROP TABLE #tmp_indexes

    set nocount off

  • Lowell

    SSC Guru

    Points: 323450

    wow this script has come a long way since the original version; thanks again to everyone who has contributed;

    I cleaned up the formatting a little bit and added some comments, so that folks run this against SQL 2005 don't panic when it fails due to the new filtered indexes;

    --Script SQL 2008+ Indexes AS CREATE INDEX Statements

    --http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx

    --original version submitted by anonymous/unknown contributor

    --enhanced and contributed to discussion by Lowell

    --enhanced by thorv-918308 to correct index columns in the correct sequence

    --enhanced by thorv-918308 added fill factor, and with ONLINE = ON

    --enhanced by mfuller333 to include DROP statements!

    --enhanced by Sander A. to include filtered indexes!

    --enhanced by craigborri to put the clustered indexes first, you'll want to do if you ever run this for real

    /*if you get this error:

    Msg 207, Level 16, State 1, Line 22

    Invalid column name 'filter_definition'.

    Msg 207, Level 16, State 1, Line 24

    Invalid column name 'filter_definition'.

    Msg 208, Level 16, State 0, Line 11

    Invalid object name '#tmp_indexes'.

    the issue is the database in question is SQL 2005: filter_definition is SQL 2008

    see the inline code to comment/uncomment to make this SQL 2005 compliant.

    */

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

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

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

    SELECT

    tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),

    tableid = ixz.object_id,

    indexid = ixz.index_id,

    indexname = ixz.name,

    isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),

    isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),

    indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),

    --SQL2008+ Filtered indexes:

    CASE

    WHEN ixz.filter_definition IS NULL

    THEN ''

    ELSE ' WHERE ' + ixz.filter_definition

    END Filter_Definition

    --For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:

    --'' AS Filter_Definition

    INTO #tmp_indexes

    FROM sys.indexes ixz

    INNER JOIN sys.objects obz

    ON ixz.object_id = obz.object_id

    INNER JOIN sys.schemas scmz

    ON obz.schema_id = scmz.schema_id

    WHERE ixz.index_id > 0

    AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index

    AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0 -- comment out to include unique and

    AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include PK's

    --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

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

    --Cursor Block

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

    OPEN index_cursor

    FETCH NEXT FROM index_cursor INTO @tableid, @indexid

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SELECT @isql_key = '', @isql_incl = ''

    SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *

    --key column

    @isql_key = CASE ixcolz.is_included_column

    WHEN 0

    THEN CASE ixcolz.is_descending_key

    WHEN 1

    THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '

    ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '

    END

    ELSE @isql_key

    END,

    --include column

    @isql_incl = CASE ixcolz.is_included_column

    WHEN 1

    THEN CASE ixcolz.is_descending_key

    WHEN 1

    THEN @isql_incl + COALESCE(colz.name,'') + ', '

    ELSE @isql_incl + COALESCE(colz.name,'') + ', '

    END

    ELSE @isql_incl

    END

    FROM sysindexes ixz

    INNER JOIN sys.index_columns AS ixcolz

    ON (ixcolz.column_id > 0

    AND ( ixcolz.key_ordinal > 0

    OR ixcolz.partition_ordinal = 0

    OR ixcolz.is_included_column != 0)

    )

    AND ( ixcolz.index_id=CAST(ixz.indid AS INT)

    AND ixcolz.object_id=ixz.id

    )

    INNER JOIN sys.columns AS colz

    ON colz.object_id = ixcolz.object_id

    AND colz.column_id = ixcolz.column_id

    WHERE ixz.indid > 0 AND ixz.indid < 255

    AND (ixz.status & 64) = 0

    AND ixz.id = @tableid

    AND ixz.indid = @indexid

    ORDER BY

    ixz.name,

    CASE ixcolz.is_included_column

    WHEN 1

    THEN ixcolz.index_column_id

    ELSE ixcolz.key_ordinal

    END

    --remove any trailing commas from the cursor results

    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)

    --put the columns collection into our temp table

    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 --WHILE

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

    --End Cursor Block

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

    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 for results-to-text

    SELECT '---------------------------------------------------------------------'

    --create index scripts (for backup)

    SELECT 'IF NOT EXISTS(SELECT OBJECT_ID(' + INDEXNAME + ')' + ' ' +

    'CREATE '

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

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

    + 'INDEX ' + QUOTENAME(INDEXNAME)

    + ' ON ' + (TABLENAME) + ' '

    + '(' + keycolumns + ')'

    + CASE

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

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

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

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

    ELSE ' INCLUDE(' + INCLUDES + ') ' + Filter_Definition + ' 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

    ISCLUSTERED desc,

    tablename,

    indexid,

    indexname

    --makes the drop

    SELECT

    'DROP INDEX '

    + ' ' + (tablename) + '.'

    + (indexname) + ''

    FROM #tmp_indexes

    WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_')

    --Drop the temp table again

    DROP TABLE #tmp_indexes

    SET NOCOUNT OFF

    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!

  • upretyd

    SSC Journeyman

    Points: 91

    --Script SQL 2008+ Indexes AS CREATE INDEX Statements

    --http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx

    --original version submitted by anonymous/unknown contributor

    --enhanced and contributed to discussion by Lowell

    --enhanced by thorv-918308 to correct index columns in the correct sequence

    --enhanced by thorv-918308 added fill factor, and with ONLINE = ON

    --enhanced by mfuller333 to include DROP statements!

    --enhanced by Sander A. to include filtered indexes!

    --enhanced a tiny bit to include filegroups. Does not script partitioned indexes for now.

    /*if you get this error:

    Msg 207, Level 16, State 1, Line 22

    Invalid column name 'filter_definition'.

    Msg 207, Level 16, State 1, Line 24

    Invalid column name 'filter_definition'.

    Msg 208, Level 16, State 0, Line 11

    Invalid object name '#tmp_indexes'.

    the issue is the database in question is SQL 2005: filter_definition is SQL 2008

    see the inline code to comment/uncomment to make this SQL 2005 compliant.

    */

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

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

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

    SELECT

    ixz.object_id,

    tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),

    tableid = ixz.object_id,

    indexid = ixz.index_id,

    indexname = ixz.name,

    isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),

    isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),

    indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),

    --SQL2008+ Filtered indexes:

    CASE

    WHEN ixz.filter_definition IS NULL

    THEN ''

    ELSE ' WHERE ' + ixz.filter_definition

    END Filter_Definition

    --For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:

    --'' AS Filter_Definition

    INTO #tmp_indexes

    FROM sys.indexes ixz

    INNER JOIN sys.objects obz

    ON ixz.object_id = obz.object_id

    INNER JOIN sys.schemas scmz

    ON obz.schema_id = scmz.schema_id

    WHERE ixz.index_id > 0

    AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index

    AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0 -- comment out to include unique and

    AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include PK's

    --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

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

    --Cursor Block

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

    OPEN index_cursor

    FETCH NEXT FROM index_cursor INTO @tableid, @indexid

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SELECT @isql_key = '', @isql_incl = ''

    SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *

    --key column

    @isql_key = CASE ixcolz.is_included_column

    WHEN 0

    THEN CASE ixcolz.is_descending_key

    WHEN 1

    THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '

    ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '

    END

    ELSE @isql_key

    END,

    --include column

    @isql_incl = CASE ixcolz.is_included_column

    WHEN 1

    THEN CASE ixcolz.is_descending_key

    WHEN 1

    THEN @isql_incl + COALESCE(colz.name,'') + ', '

    ELSE @isql_incl + COALESCE(colz.name,'') + ', '

    END

    ELSE @isql_incl

    END

    FROM sysindexes ixz

    INNER JOIN sys.index_columns AS ixcolz

    ON (ixcolz.column_id > 0

    AND ( ixcolz.key_ordinal > 0

    OR ixcolz.partition_ordinal = 0

    OR ixcolz.is_included_column != 0)

    )

    AND ( ixcolz.index_id=CAST(ixz.indid AS INT)

    AND ixcolz.object_id=ixz.id

    )

    INNER JOIN sys.columns AS colz

    ON colz.object_id = ixcolz.object_id

    AND colz.column_id = ixcolz.column_id

    WHERE ixz.indid > 0 AND ixz.indid < 255

    AND (ixz.status & 64) = 0

    AND ixz.id = @tableid

    AND ixz.indid = @indexid

    ORDER BY

    ixz.name,

    CASE ixcolz.is_included_column

    WHEN 1

    THEN ixcolz.index_column_id

    ELSE ixcolz.key_ordinal

    END

    --remove any trailing commas from the cursor results

    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)

    --put the columns collection into our temp table

    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 --WHILE

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

    --End Cursor Block

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

    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 for results-to-text

    SELECT '---------------------------------------------------------------------'

    --create index scripts (for backup)

    --IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N' + '''' +

    -- '[dbo].[' + tablename + ']' + '''' +

    --') AND name = N' + '''' +

    -- indexname + '''' + ')' +

    SELECT

    'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + ti.TABLENAME + '''' + ') AND name = N' + '''' + ti.INDEXNAME + '''' + ')' + ' ' +

    'CREATE '

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

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

    + 'INDEX ' + QUOTENAME(ti.INDEXNAME)

    + ' ON ' + (ti.TABLENAME) + ' '

    + '(' + ti.keycolumns + ')'

    + CASE

    WHEN ti.INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 1 AND INCLUDES = '' THEN ti.Filter_Definition + ' WITH (SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'

    WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'

    WHEN INDEXFILLFACTOR <> 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ') ON [' + fg.name + ']'

    WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES <> '' THEN ' INCLUDE (' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'

    ELSE ' INCLUDE(' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ', ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'

    END

    FROM #tmp_indexes ti

    JOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.name

    JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id

    WHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tables

    ORDER BY

    ti.tablename,

    ti.indexid,

    ti.indexname

    --makes the drop

    SELECT

    'DROP INDEX '

    + ' ' + (tablename) + '.'

    + (indexname) + ''

    FROM #tmp_indexes

    WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_')

    ----Drop the temp table again

    DROP TABLE #tmp_indexes

    --SET NOCOUNT OFF

  • craigborri

    Valued Member

    Points: 66

    I'd suggest changing the order by on the last select to

    order by ISCLUSTERED desc, tablename, indexid, indexname

    This will insure the clustered indexes are created first, which you'll want to do if you ever run this for real.

Viewing 15 posts - 1 through 15 (of 23 total)

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