Search This Blog

find the tables having composite clustered index

script to find the tables having composite clustered index

;WITH cte AS (
SELECT   OBJECT_NAME(i.object_id) NAME, type_desc, i.index_id ,c.name columnName ,p.rows 
FROM sys.indexes i 
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c on ic.column_id = c.column_id AND c.object_id = i.object_id
WHERE i.type = 1 
AND OBJECT_NAME(i.object_id) NOT LIKE  'sys%'
),
cte2 AS (
SELECT *, rn = ROW_NUMBER()OVER ( PARTITION BY NAME ORDER BY columnName desc )
FROM cte 
)
SELECT DISTINCT name, rows 
FROM cte2 b 
WHERE EXISTS (SELECT 1 FROM cte2 a WHERE a.name = b.name AND a.rn >=2 )
ORDER BY 2 

No comments: