Thursday, April 21, 2011

Find All The Columns in Indexes with SQL Azure

In SQL Server on-premise, this query is often used to find all the columns that are currently participating in an index:

SELECT OBJECT_NAME(sysindexkeys.id) Table_Name,
            
syscolumns.name Column_Name,
            
sysindexes.name Index_Name,
    
CASE WHEN
                
INDEXKEY_PROPERTY(sysindexes.id, sysindexes.indid, sysindexkeys.keyno, 'IsDescending')=0
                
THEN 'Ascending'
    
ELSE    'Descending'
    
END SORT_ORDER
    
FROM sysindexkeys
            
INNER JOIN syscolumns
            
ON sysindexkeys.colid=syscolumns.colid
        
AND sysindexkeys.id=syscolumns.id
            
INNER JOIN sysindexes
            
ON sysindexkeys.indid=sysindexes.indid
        
AND sysindexkeys.id=sysindexes.id
            



In SQL Azure, we don't have sysindexes or sysindexkeys. Instead, we use sys.indexes and sys.index_columms. It looks like this:

SELECT OBJECT_NAME(sysindexkeys.OBJECT_ID) Table_Name,
            
syscolumns.name Column_Name,
            
sysindexes.name Index_Name,
    
CASE WHEN is_descending_key = '0' THEN 'Ascending'
    
ELSE 'Descending'
    
END AS SORT_ORDER,
            
type_desc AS indexType
    
FROM sys.index_columns sysindexkeys
            
INNER JOIN syscolumns
            
ON sysindexkeys.index_column_id=syscolumns.colid
        
AND sysindexkeys.OBJECT_ID=syscolumns.id
            
INNER JOIN sys.indexes sysindexes
            
ON sysindexkeys.index_id=sysindexes.index_id
        
AND sysindexkeys.OBJECT_ID=sysindexes.OBJECT_ID
            

No comments:

Post a Comment