Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do we know whether or not a column is involved in an index? Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 3:20 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
We have so many indices in a table. Is there any script to easily find whether or not a column is involved in any existing index?

Many thanks in advance for any input.
Post #1434053
Posted Thursday, March 21, 2013 3:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
Check out sys.index_columns.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434056
Posted Thursday, March 21, 2013 3:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 21,644, Visits: 15,317
This has limited testing - you can try it.

SELECT o.name AS ObjName,i.name AS IdxName,i.index_id
,c.name AS ColName,ic.column_id AS IxColumnID
FROM sys.objects o
INNER JOIN sys.columns c
ON c.object_id = o.object_id
LEFT OUTER JOIN sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id
ON o.object_id = i.object_id
AND i.index_id NOT IN (0,1)
WHERE OBJECTPROPERTY(c.object_id,'IsMSShipped') = 0
ORDER BY IdxName DESC

This will show all columns whether indexed or not. Columns not in indexes will display with null values.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1434062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse