Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Unhappy Unanswered: How to test if Index exists?

    How can I test if a table already has a specific index present?

    Id normally test for an object with: if object_ID('My Object') is null then...

    What technique works with an index? Or can you use the object_Id function, how do you reference the index?


    Many thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can check if a table has an index at all, not a specific index, unless you want to test for an existance of the name:

    if exists (select 1 from dbo.sysindexes where object_name(id)='your_table' and indid between 2 and 254) print 'There is at least 1 non-clustered index'
    else print 'No non-clustered indexes found'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    IF INDEXPROPERTY ( OBJECT_ID('your_table') , 'your_table_index' , 'IndexID' ) IS NULL

    If you know the name the above should work; I have not tested it.

    Tim S

  4. #4
    Join Date
    Feb 2005
    Location
    London
    Posts
    19
    sp_helpindex tableName

    OR

    IF EXISTS (SELECT indid
    FROM sysindexes
    WHERE id = OBJECT_ID('tableName')
    AND name = 'indexName') THEN

    .....
    Last edited by jay82; 02-23-05 at 12:28.

Posting Permissions

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