SQL Server - Catalog Queries 2005/2008

Primary Key / Index Information for a SQL Server 2005/2008 Table

This query will return details pertaining to the primary key and indexes defined on a SQL Server 2005/2008 table.

Query

SELECT 
       indexes.name                  , 
       all_columns.name              ,
       indexes.type_desc             ,            
       indexes.index_id              , 
       indexes.type                  , 
       indexes.is_unique             , 
       index_columns.index_column_id , 
       index_columns.column_id       ,
       index_columns.is_descending_key ,
       index_columns.key_ordinal            
FROM   SYS.indexes
JOIN sys.index_columns on 
     indexes.index_id = index_columns.index_id and
     indexes.object_id = index_columns.object_id
join sys.all_columns 
     on all_columns.column_id = index_columns.index_column_id 
     and
     all_columns.object_id = index_columns.object_id
WHERE indexes.object_id = 53575229 
order by indexes.index_id,  indexes.type, key_ordinal

Using this query

You will need to run the query Get List of Tables to obtain the specific object_id that you will need to replace in the query above.

 Collapse All    Expand All

• SQL Server 2005/2008
  • List User Tables
  • Create Table Info
  • Prikey/Index Info
  • List FK Contraints
  • List Check Constraints
  • List Default Contstraints

SQL Server Information and Resources