Professional Web Applications Themes

Check For Indexes - Microsoft SQL / MS SQL Server

Hi All, I have a DTS package that takes a considerable length of time to run. I've managed to get the time down to around three hours by adding a number of indexes to the various tables. This is fine. However, the tables are re-created each week, without indexes. I have no control over the table creation at all! So I have added a TSQL task to the front of my DTS to create the indexes. This is all fine as well, but due to the fact that the DTS may be run more than once a week I need ...

  1. #1

    Default Check For Indexes

    Hi All,

    I have a DTS package that takes a considerable length of time to run. I've
    managed to get the time down to around three hours by adding a number of
    indexes to the various tables. This is fine.

    However, the tables are re-created each week, without indexes. I have no
    control over the table creation at all! So I have added a TSQL task to the
    front of my DTS to create the indexes. This is all fine as well, but due to
    the fact that the DTS may be run more than once a week I need to add in some
    checks to see if the indexes exist before trying to create them.

    As yet I've found no good TSQL method for check in the existence of the
    indexes. I've found sphelpindex, but am unsure as to how to 'trap' or query
    the results. Does anyone have any suggestions please? I know the names of
    the indexes, so what I would really like to do is derive a 'SELECT'
    statement where I can count the output. If the count is >0 then I don't
    create the index if it isn't I do.

    Any help/suggestions would be much appreciated.

    Thanks,

    Steve


    Steve Guest

  2. #2

    Default Re: Check For Indexes

    IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = <index name>)

    CREATE INDEX ...etc

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Steve" <net> wrote in message
    news:phx.gbl... 
    I've 
    the 
    to 
    some 
    query 
    of 


    Jacco Guest

  3. #3

    Default Re: Check For Indexes


    create table test
    (
    ID int not null
    , field char(10)
    )

    alter table test add constraint IX_test_field unique nonclustered(field)
    alter table test add constraint PK_test_ID PRIMARY KEY (ID)

    if exists(select
    *
    from sysindexes
    where object_id('test') = id
    and name = 'IX_test_field')
    begin
    print 'There is a index named ''IX_test_fieled'' in table ''test'' and will be deleted!'
    alter table test drop constraint IX_test_field
    print '...'
    print 'Index named ''IX_test_fieled'' in table ''test'' has been deleted!'
    end

    drop table test

    --
    Dean Savovic
    www.teched.hr


    "Steve" <net> wrote in message news:phx.gbl... 


    Dean Guest

Similar Threads

  1. indexes in cs
    By Albert_Constantineau@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 1
    Last Post: August 14th, 09:41 PM
  2. using indexes
    By tragik in forum Coldfusion Database Access
    Replies: 0
    Last Post: January 9th, 07:03 PM
  3. Indexes
    By tomL in forum Dreamweaver AppDev
    Replies: 2
    Last Post: March 14th, 04:26 PM
  4. Two indexes?
    By Andy_Fielding@adobeforums.com in forum Adobe Indesign Windows
    Replies: 1
    Last Post: August 14th, 02:55 AM
  5. set indexes enabled
    By John Carlson in forum Informix
    Replies: 0
    Last Post: June 25th, 05:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139