SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

Getting all the Clustered keys in the database

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 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

Comments

 

Saggi Neumann said:

Hey,

Nice script!

You'd probably want to replace the following part of your script:

SUBSTRING( (        SELECT ... FOR XML ... ),2,9999)

with a neater solution:

STUFF ( ( SELECT ... FOR XML ...) ,1,1,'')

which simply replaces the first character (',') with an empty string.

Cheers!

March 30, 2009 7:49 PM
 

leo.pasta said:

Hi Saggi,

Thanks for the feedback, I've updated the script. Indeed your suggestion is more elegant and foolproof.

March 30, 2009 10:57 PM
Powered by Community Server (Commercial Edition), by Telligent Systems