Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Question Unanswered: checking if a table has index on it in ms sql server7

    I am using MS sql 7 as database with ASP as front end. in the front end i want to check if a particular table has an index on it. if yes i want to drop index, truncate the table, insert new values into the table and recreate index. this i am doing to make inserting values faster.
    in case the front end is being run for first time ever.index would not be present. so if i do drop index in front end script i would get error.
    so in this case drop index should not be done and directly table shd be truncated(step 2)
    so how can i check if this particular table has an index already created.. any sql query that can send a reply of presence or absence of index in sql server7? any thing related to sysindexes?

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    indid on sysindexes could be used to get this info -

    if 0 then no clustered index, but nonclustered indexes may exist
    if 255 then clustered index
    if >0 and <255 then it is a nonclustered index

    So, logic would be something like -
    if table has an indid = 0 and no other rows exist in sysindexes for that table then it has no indexes.
    Regards
    Dbabren

  3. #3
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    sorry clustered index indid = 1 (not 255 as I said prev)
    Regards
    Dbabren

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    From The BOL (The Holy Book) -
    Sysindexes table
    id -

    ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.

    indid -

    ID of index:
    1 = Clustered index
    >1 = Nonclustered
    255 = Entry for tables that have text or image data
    So you can query the sysindexes table for the desired results

    or you can simply use

    sp_helpindex your_table_name
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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