Post reply

How to query index_id and index_name? And base index?

  • February 27, 2009 at 9:00 am

    #71246

    Using the DBCC SHOWCONTIG ('TABLENAME')

    My book states that If the Optional Name or ID of the index is not provided the DBCC Command analyzes the 'base index' for the table or view.

    Two questions:

    How may I list (query) the index_id and index_name for a table?

    What is the 'base index' for a table? Is it the first index that was created for the table or is it the primary key index?

    Thanks, Kevin

  • February 27, 2009 at 9:16 am

    #951249

    kevinsql7 (2/27/2009)

    Two questions:

    How may I list (query) the index_id and index_name for a table?

    If you're asking for all the indexes (with name and id) on the table then

    select index_id, name from sys.indexes where object_id = OBJECT_ID(@TableName)

    What is the 'base index' for a table?

    The clustered index, if one exists. If not, it'll be the heap. (index id 0 or 1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2009 at 11:53 am

    #951329

    Question,

    OBJECT_ID seems to be unique to SQL Server 2005. How would you get this query to work for SQL Server 2000?

    Thanks, Kevin

  • February 27, 2009 at 12:18 pm

    #951346

    kevinsql7 (2/27/2009)

    OBJECT_ID seems to be unique to SQL Server 2005.

    Most certainly not. It's been around since SQL 7, if not before.

    http://msdn.microsoft.com/en-us/library/aa276843(SQL.80).aspx

    Please post SQL 2000-related questions in the SQL 2000 forums. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.

    For SQL 2000, replace sys.indexes with sysindexes, object_id with id and index_id with indid

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2009 at 12:35 pm

    #951354

    Thanks. I had to put the tablename in single quotes. Sorry about posting a SQL 2000 Question on the SQL 2005 Forum.

    Kevin

  • February 27, 2009 at 12:50 pm

    #951360

    kevinsql7 (2/27/2009)

    Thanks. I had to put the tablename in single quotes.

    Yup. The example I posted was with a variable as a parameter. A string literal would go in quotes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply