List of all index & index columns in SQL Server DB

by

There are two “sys” catalog views you can consult: sys.indexes and sys.index_columns.

Those will give you just about any info you could possibly want about indices and their columns.

EDIT: This query’s getting pretty close to what you’re looking for:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

More Related Contents:

  1. SQL Server: Index columns used in like?
  2. T-SQL for finding Redundant Indexes
  3. SQL Server – error when creating stored procedure
  4. How can foreign key constraints be temporarily disabled using T-SQL?
  5. What do Clustered and Non-Clustered index actually mean?
  6. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  7. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  8. Parameter Sniffing (or Spoofing) in SQL Server
  9. SQL Server String or binary data would be truncated
  10. How to change identity column values programmatically?
  11. How do you count the number of occurrences of a certain substring in a SQL varchar?
  12. SQL Server Regular expressions in T-SQL
  13. Is there a way to loop through a table variable in TSQL without using a cursor?
  14. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  15. How do I format a number with commas in T-SQL?
  16. Concatenating Column Values into a Comma-Separated List
  17. Find index of last occurrence of a sub-string using T-SQL
  18. What columns generally make good indexes?
  19. SQL Server 2008 Empty String vs. Space
  20. How to set a default value for an existing column
  21. Need a datetime column in SQL Server that automatically updates when the record is modified
  22. How do I find a stored procedure containing ?
  23. WHERE clause on SQL Server “Text” data type
  24. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  25. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  26. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  27. Cannot bulk load. Operating system error code 5 (Access is denied.)
  28. Does query plan optimizer works well with joined/filtered table-valued functions?
  29. Is there an “IF EXISTS” test for a linked server?
  30. SQL Server stored procedure return code oddity

Leave a Comment