Jeremiah Peschka (http://twitter.com/peschkaj) asked on twitter if anyone had a script to list all clustered keys in the database. This is my take on it, I hope it helps someone:
SELECT i.name , '(' +
STUFF( (
SELECT ',' + c.name + CASE WHEN is_descending_key = 0 THEN ' ASC '
ELSE ' DESC ' END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID
AND ic.column_id = c.column_id
WHERE i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
AND key_ordinal > 0
ORDER BY key_ordinal
FOR XML PATH('') --This is a trick that generated comma-separated lists (but begining with a spurious comma)
) ,1,1,''
) + ')'+
ISNULL('(' + STUFF((
SELECT ',' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID
AND ic.column_id = c.column_id
WHERE i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY key_ordinal
FOR XML PATH('')) + ')',1,1,' INCLUDE ( '),'')
+ ' ON [' +
(
SELECT CASE
WHEN i.data_space_id < 65000 THEN (
SELECT d.name
FROM sys.data_spaces d
WHERE i.data_space_id = d.data_space_id)
ELSE (
SELECT fg.name
FROM sys.data_spaces d
JOIN sys.destination_data_spaces dd ON d.data_space_id = dd.partition_scheme_id
AND dd.destination_id = 1
JOIN sys.data_spaces fg ON dd.data_space_id = fg.data_space_id
WHERE i.data_space_id = d.data_space_id)
END
) + ']'
FROM sys.indexes i
WHERE i.type_desc = 'CLUSTERED'
AND OBJECTPROPERTY(i.OBJECT_ID,'isUserTable') = 1