You must Sign In to post a response.
  • Category: SQL Server

    How to get table Index

    Hi i need query for get table index count and index names which are used for that table

    thanks
    thyaga
  • #747411
    Hope this helps

    select count(*),W.*
    from
    (
    select ind.name
    from
    sys.indexes ind
    inner join
    sys.tables t ON ind.object_id = t.object_id
    where t.name = 'Table1'
    )W

  • #747412
    SELECT ind.name,
    ind.type_desc,
    obj.name
    FROM sys.indexes ind
    JOIN sys.objects obj
    ON ind.object_id = obj.object_id
    WHERE obj.type = 'U'

  • #747445
    Most of the SQL server versions has 'INFORMATION_SCHEMA' that contains the index information for a particular table. I think you can do it using INFORMATION_SCHEMA.TABLE_CONSTRAINTS or INFORMATION_SCHEMA.KEY_COLUMN_USAGE.
    just fire following query to get result

    select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


  • Sign In to post your comments