Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: I need a query to find indexes names for some kind of tables

    Hi,
    I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    In sql2000 you can query the sysindexes table. In 2005 I think it is sys.sysindexes.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also see sp_helpindex

  4. #4
    Join Date
    Mar 2008
    Posts
    3
    Quote Originally Posted by Thrasymachus
    In sql2000 you can query the sysindexes table. In 2005 I think it is sys.sysindexes.
    ya..tnks..i saw columns in sysindexes table.. but it contains a column called"name" which has either index name or table name but not both..If a table doesn't have an index then it is available in name column.. But i want output as both index name and its corresponding table should be displayed..
    Can pls help me regarding...

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can join sys.indexes with sys.objects on the id column in both tables.

    sys.indexes.name has the name of the index, sys.objects.name has the name of the table.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select * from sys.indexes where object_name(id) in (<list_of_table_names>)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    where object_name(id)
    That's a clever little trick - thanks for that
    George
    Home | Blog

Posting Permissions

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