Home Forums Article Discussions Article Discussions by Author Discuss content posted by Lowell Script all indexes as CREATE INDEX statements RE: Script all indexes as CREATE INDEX statements

  • May 24, 2011 at 9:20 am

    #1329150

    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!