I want to check whether all the tables in my Database has clusterd index on which type of column(eg:int) and whether the clustered index is on a single/multipe columns. I was not able to figure out which DMvs or Views to retreive this information. Please help.
Asked
Active
Viewed 50 times
3 Answers
1
below sql will give you list of the clusterd indexes and table name with few more detail. you can modified this to get to you results.
SELECT 'ClusteredIndexName' = SI.Name,
'TableName' = SO.Name,
'ColumCount' = IK.IndexColumnCount,
'IsUnique' = CASE WHEN SI.is_unique = 0 THEN 'N' ELSE 'Y' END
,SI.type_desc
FROM SYS.INDEXES SI
JOIN SYS.OBJECTS SO -- Joining on SYS.OBJECTS to get the TableName
ON SO.OBJECT_ID = SI.Object_ID
JOIN ( -- Joining on a Derived view to work out how many columns exist on the clustered index
SELECT 'IndexColumnCount' = MAX(KEY_ORDINAL), OBJECT_ID, index_id
FROM SYS.INDEX_COLUMNS
GROUP BY OBJECT_ID, index_id
) AS IK
ON IK.object_id = SI.Object_ID
AND IK.index_id = SI.index_id
WHERE SI.type_desc = 'CLUSTERED' and
SI.OBJECT_ID NOT IN (SELECT OBJECT_ID
FROM SYS.ALL_OBJECTS
WHERE TYPE = 'S') -- Not system tables
AND SO.Type = 'U'
AND SO.is_ms_shipped = 0
Hiten004
- 2,425
- 1
- 22
- 34
-
Thank you very much... This is what I was looking for. Shall we find the data type of the column(s) inculded in the index? – RMu Nov 11 '15 at 16:22
-
Please look this post http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db – Hiten004 Nov 11 '15 at 17:44
0
Sql Server Management Studio lets you see the definition of all indexes on a table. Find the table in the Tables folder, expand, and expand Indexes. Select Properties on a particular index to view columns and other properties.
Matt Perkins
- 46
- 4
-
Yes I know but i have a huge database with lot of indexes. Instead of searching through all of it do I have a query to get the information at once? – RMu Nov 10 '15 at 18:47
