Total Page Preview:   000000000639

SQL Server - Find all index with key columns on Azure SQL Database

In this article we will learn how to find all index on database with key column and include column in azure sql database or sql server database.

Find Index With key Column In Azure SQL Server Database
 

 
SELECT TOP 50 
        '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName
    ,Ind.type_desc AS IndexType
    ,Ind.[name] AS IndexName
    ,SUBSTRING((SELECT  ', ' + AC.[name] FROM sys.[tables] AS T
                           INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
                INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
                INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
                WHERE   Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 0
                ORDER BY IC.key_ordinal FOR XML PATH('')), 2, 8000) AS KeyColumns
 
    ,SUBSTRING((SELECT  ', ' + AC.[name] FROM    sys.[tables] AS T
                 INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
                 INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
                 INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]   AND IC.[column_id] = AC.[column_id]
                 WHERE   Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 1
                 ORDER BY IC.key_ordinal FOR XML PATH('') ), 2, 8000) AS IncludeColumns
 
        ,usg_stats.user_seeks AS UserSeek
        ,usg_stats.user_scans AS UserScans
        ,usg_stats.user_lookups AS UserLookups
        ,usg_stats.user_updates AS UserUpdates
             ,o.create_date
             ,usg_stats.last_user_scan
             ,usg_stats.last_system_seek
           
FROM    sys.[indexes] AS Ind
             INNER JOIN sys.objects o on o.[object_id] = Ind.[object_id]
        INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
        LEFT JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id
        AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID()
WHERE  Ind.type_desc <> 'HEAP' --AND Tab.name  = 'YourTableName' -- uncomment to get single table indexes detail
ORDER BY o.create_date desc

 
Find Index With Column In Azure SQL Server Database

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments







Facebook User: