March 21, 2013 at 3:20 pm
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.
March 21, 2013 at 3:26 pm
Check out sys.index_columns.
March 21, 2013 at 3:43 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy