Script all indexes as CREATE INDEX statements

  • SQLRNNR

    SSC Guru

    Points: 281243

    Wow this script has really come a long way - even better than the last time I looked. I'm gonna need to compare to a different script I have with similar functionality now.

    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

  • patelekta

    SSC Rookie

    Points: 48

    May be you will get your answer on http://ektaraval.blogspot.com/2010/07/query-to-generate-create-index.html

  • Lowell

    SSC Guru

    Points: 323460

    patelekta (5/24/2011)


    May be you will get your answer on http://ektaraval.blogspot.com/2010/07/query-to-generate-create-index.html%5B/quote%5D

    ekta your example only works if an index has one and only one column in it's definition...otherwise it scripts two definitions for the same index, but with different columns.

    for example, compare the results if a single index from the above script to yours:

    You'll want to enhance your version, maybe use the FOR XML to append all the columns together for the definition to really make yours valid for all indexes.

    IF NOT EXISTS(SELECT OBJECT_ID(IX_GMATDET_HELP)

    CREATE INDEX [IX_GMATDET_HELP] ON [dbo].[GMATDET] (

    ACTTRANSDETTBLKEY ASC,

    SOURCETBLKEY ASC,

    YEARTBLKEY ASC,

    SETASDTBLKEY ASC,

    SUBGRANTTBLKEY ASC,

    TRANSACTIONAMT ASC,

    RECEIPT1AMT ASC,

    RECEIPT2AMT ASC,

    RECEIPT3AMT ASC,

    ADJUSTMENT ASC) WITH (ONLINE = ON)

    --your script is generating the same indexname for each column in the definition,

    -- instead of one index featuring all columns.

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ACTTRANSDETTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SOURCETBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [YEARTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SETASDTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SUBGRANTTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [TRANSACTIONAMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT1AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT2AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT3AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ADJUSTMENT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    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!

  • Paul Heiner

    SSC Enthusiast

    Points: 166

    Code fix

    It's already been commented on once, but the code has not been modified

    --ORDER BY SYSCOLUMNS.COLID - This is the original code -- it will create the composite indexes with the order of columns WRONG Tested on SQL 2000 SQL 2008

    ORDER BY SYSINDEXKEYS.keyno - It should be

    Still a great script, saved me time, another example of test - test - test before you run a downloaded script.

  • Lowell

    SSC Guru

    Points: 323460

    Paul take a look at this post here in the Discussions thread;

    http://www.sqlservercentral.com/Forums/FindPost1079779.aspx

    That post ( a few posts above this) has a much more modern scripting style that correctly takes into consideration the column order on the index.

    the original script was contributed when SQL 2000 was the target database version...it's come a long way since then

    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!

  • Mohammed Imran Ali

    SSCrazy

    Points: 2125

    That was really helpfull.

    I was trying to execute on SQL Server 2008 R2 and bumped into an issue with the Collation parameters with the "keycolumns ".

    To work around, I added COLLATE DATABASE_DEFAULT for keycolumns in the sections that generates the CREATE INDEX scripts.

  • hrothenb

    SSC-Addicted

    Points: 427

    SELECT ' CREATE ' +

    CASE

    WHEN I.is_unique = 1 THEN ' UNIQUE '

    ELSE ''

    END +

    I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +

    I.name + ' ON ' +

    SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +

    KeyColumns + ' ) ' +

    ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +

    ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +

    CASE

    WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '

    ELSE ' PAD_INDEX = OFF '

    END + ',' +

    'FILLFACTOR = ' + CONVERT(

    CHAR(5),

    CASE

    WHEN I.fill_factor = 0 THEN 100

    ELSE I.fill_factor

    END

    ) + ',' +

    -- default value

    'SORT_IN_TEMPDB = OFF ' + ',' +

    CASE

    WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '

    ELSE ' IGNORE_DUP_KEY = OFF '

    END + ',' +

    CASE

    WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '

    ELSE ' STATISTICS_NORECOMPUTE = ON '

    END + ',' +

    ' ONLINE = OFF ' + ',' +

    CASE

    WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '

    ELSE ' ALLOW_ROW_LOCKS = OFF '

    END + ',' +

    CASE

    WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '

    ELSE ' ALLOW_PAGE_LOCKS = OFF '

    END + ' ) ON [' +

    DS.name + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]

    FROM sys.indexes I

    JOIN sys.tables T

    ON T.object_id = I.object_id

    JOIN sys.sysindexes SI

    ON I.object_id = SI.id

    AND I.index_id = SI.indid

    JOIN (

    SELECT *

    FROM (

    SELECT IC2.object_id,

    IC2.index_id,

    STUFF(

    (

    SELECT ' , ' + C.name + CASE

    WHEN MAX(CONVERT(INT, IC1.is_descending_key))

    = 1 THEN

    ' DESC '

    ELSE

    ' ASC '

    END

    FROM sys.index_columns IC1

    JOIN sys.columns C

    ON C.object_id = IC1.object_id

    AND C.column_id = IC1.column_id

    AND IC1.is_included_column =

    0

    WHERE IC1.object_id = IC2.object_id

    AND IC1.index_id = IC2.index_id

    GROUP BY

    IC1.object_id,

    C.name,

    index_id

    ORDER BY

    MAX(IC1.key_ordinal)

    FOR XML PATH('')

    ),

    1,

    2,

    ''

    ) KeyColumns

    FROM sys.index_columns IC2

    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables

    GROUP BY

    IC2.object_id,

    IC2.index_id

    ) tmp3

    )tmp4

    ON I.object_id = tmp4.object_id

    AND I.Index_id = tmp4.index_id

    JOIN sys.stats ST

    ON ST.object_id = I.object_id

    AND ST.stats_id = I.index_id

    JOIN sys.data_spaces DS

    ON I.data_space_id = DS.data_space_id

    JOIN sys.filegroups FG

    ON I.data_space_id = FG.data_space_id

    LEFT JOIN (

    SELECT *

    FROM (

    SELECT IC2.object_id,

    IC2.index_id,

    STUFF(

    (

    SELECT ' , ' + C.name

    FROM sys.index_columns IC1

    JOIN sys.columns C

    ON C.object_id = IC1.object_id

    AND C.column_id = IC1.column_id

    AND IC1.is_included_column =

    1

    WHERE IC1.object_id = IC2.object_id

    AND IC1.index_id = IC2.index_id

    GROUP BY

    IC1.object_id,

    C.name,

    index_id

    FOR XML PATH('')

    ),

    1,

    2,

    ''

    ) IncludedColumns

    FROM sys.index_columns IC2

    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables

    GROUP BY

    IC2.object_id,

    IC2.index_id

    ) tmp1

    WHERE IncludedColumns IS NOT NULL

    ) tmp2

    ON tmp2.object_id = I.object_id

    AND tmp2.index_id = I.index_id

    WHERE I.is_primary_key = 0

    AND I.is_unique_constraint = 0

    --AND I.Object_id = object_id('Person.Address') --Comment for all tables

    --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

  • R.P.Rozema

    SSChampion

    Points: 12300

    Let me add my version too. No cursors in the implementation. Many (but not all) of the latest SQL server versions' index options. Both indexes and primary keys are scripted. Plus, it also generates statements to drop the indexes and/or primary keys on the selected tables.

    Here it is. Please let me know if you notice any oversights.

    with cte as (

    select

    tbl.object_id,

    ix.index_id,

    ix.is_primary_key,

    x.create_statement,

    x.drop_statement

    from sys.tables tbl

    inner join sys.indexes ix on (ix.object_id = tbl.object_id)

    left outer join sys.key_constraints kc on (ix.is_primary_key = 1 and kc.parent_object_id = ix.object_id and kc.unique_index_id = ix.index_id and kc.type = 'PK')

    left outer join sys.data_spaces ds on (ds.data_space_id = ix.data_space_id)

    left outer join sys.stats st on (st.object_id = ix.object_id and st.stats_id = 1)

    cross apply (

    select N'create'

    + case ix.is_unique when 1 then ' unique' else '' end

    + case ix.type when 1 then ' clustered' else '' end

    + ' index ' + quotename(ix.name)

    + ' on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))

    + '('

    + stuff((

    select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]

    from sys.index_columns c

    where c.object_id = ix.object_id

    and c.index_id = ix.index_id

    and c.is_included_column = 0

    order by c.key_ordinal,

    c.index_column_id

    for xml path(''), type

    ).value('.','nvarchar(max)'), 1, 2, '')

    + ')'

    + isnull( ' include (' +

    + stuff((

    select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]

    from sys.index_columns c

    where c.object_id = ix.object_id

    and c.index_id = ix.index_id

    and c.is_included_column = 1

    order by c.key_ordinal,

    c.index_column_id

    for xml path(''), type

    ).value('.','nvarchar(max)'), 1, 2, '')

    + ')', '')

    + case ix.has_filter when 1

    then ' where ' + ix.filter_definition

    else ''

    end

    + isnull( ' with ('

    + stuff((

    select ', ' + w.txt as [text()]

    from (

    select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0

    union all select 'PAD_INDEX = ON' where not ix.is_padded = 0

    union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0

    --SORT_IN_TEMPDB = ON

    union all select 'STATISTICS_NORECOMPUTE = ON' where not isnull(st.no_recompute, 0) = 0

    --DROP_EXISTING = ON

    --ONLINE = ON

    union all select 'ALLOW_ROW_LOCKS = ON' where not ix.allow_row_locks = 0

    union all select 'ALLOW_PAGE_LOCKS = ON' where not ix.allow_page_locks = 0

    --MAXDOP =

    --DATA_COMPRESSION = NONE | ROW | PAGE

    ) w

    for xml path(''), type

    ).value('.','nvarchar(max)'), 1, 2, '') + ')'

    ,''

    )

    + isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,

    N'drop index ' + quotename(ix.name) + ' '

    + 'on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id)) as drop_statement

    where not ix.is_primary_key = 1

    union all

    select N'alter table '

    + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))

    + ' add'

    + case kc.is_system_named when 1 then '' else ' constraint ' + quotename(kc.name) end

    + ' primary key '

    + case ix.type when 1 then 'clustered ' else '' end

    + '('

    + stuff((

    select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]

    from sys.index_columns c

    where c.object_id = ix.object_id

    and c.index_id = ix.index_id

    and c.is_included_column = 0

    order by c.key_ordinal,

    c.index_column_id

    for xml path(''), type

    ).value('.','nvarchar(max)'), 1, 2, '')

    + ')'

    + isnull( ' with ('

    + stuff((

    select ', ' + w.txt as [text()]

    from (

    select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0

    union all select 'PAD_INDEX = ON' where not ix.is_padded = 0

    union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0

    ) w

    for xml path(''), type

    ).value('.','nvarchar(max)'), 1, 2, '') + ')'

    ,''

    )

    + isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,

    N'alter table '

    + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))

    + ' drop constraint ' + quotename(kc.name) as drop_statement

    where ix.is_primary_key = 1

    ) x

    where not tbl.is_ms_shipped = 1

    and not ix.type = 0

    )

    select x.stmt + ';

    go' as [-- script to drop and recreate all existing primary keys and indexes in the database]

    from cte ix

    cross apply (

    select 1 as stage, ix.drop_statement as stmt

    union all select 2 stage, ix.create_statement as stmt

    ) x

    --where ix.object_id = object_id('dbo.Invoices')

    order by x.stage,

    object_schema_name(ix.object_id),

    object_name(ix.object_id),

    ix.index_id * case x.stage when 1 then -1 else 1 end

    Edit: Fixed an incorrect join to sys.stats. Thanks to ChrisM@Work for notifying me.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 8 posts - 16 through 23 (of 23 total)

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