How to list indexes in SQL Server database with one SQL query ? It can be very useful to display all database indexes. Indeed, to have an idea immediately at how this SQL Server database is optimized with indexes, whether an index is A clustered or a non-clustered one.
T-SQL query to list all indexes in a SQL Server database
This query offers you a synthetic list of indexes. If shows for example if the column is a primary key if it’s unique and the type of the index used (clustered or non-clustered). Build the query in 3 steps.
- First, use a CTE (Common Table Expression) to prepare the data. The data comes from different tables in the system such as indexes, columns, and tables.
- Then, call the CTE using the XML FOR XML PATH function. The XML function allows you to retrieve and list the columns used in the index on the same line. It is possible to use another way.
- And finally grouping the data with a GROUP BY.
Here is how to manage text variables with more than 8000 characters in T-SQL.
-- Building the CTE from system tables: [sys].[indexes], [sys].[columns] and [sys].[tables] WITH LIST_INDEXS AS ( SELECT sysidxcol.[index_id] + sysidxcol.[object_id] AS [IndexId], l_t.[name] AS [Table], sysidx.[name] AS [Index], li.[name] AS [Column], sysidx.[type_desc] AS [Description], sysidx.[is_primary_key] AS [PrimaryKey], sysidx.[is_unique] AS [Unique] FROM [sys].[indexes] sysidx INNER JOIN [sys].[index_columns] sysidxcol ON sysidx.[index_id] = sysidxcol.[index_id] AND sysidx.[object_id] = sysidxcol.[object_id] INNER JOIN [sys].[columns] li ON sysidxcol.[column_id] = li.[column_id] AND sysidx.[object_id] = li.[object_id] INNER JOIN [sys].[tables] l_t ON sysidx.[object_id] = l_t.[object_id] ) -- Selection of the needed informations SELECT li.[Table], li.[Index], li.[Description], li.[PrimaryKey], li.[Unique], STUFF((SELECT ',' + a.[Column] FROM LISTE_INDEXS a WHERE li.[IndexId] = a.[IndexId] FOR XML PATH('')), 1, 1, '') AS [XMLColumns] FROM LIST_INDEXS li GROUP BY li.[IndexId], li.[Table], li.[Index], li.[Description], li.[PrimaryKey], li.[Unique] ORDER BY li.[Table] ASC, li.[PrimaryKey] DESC;
Check out this other way of listing all indexes from stackoverflow. To conclude this query displays the indexes of the current database. Check out this section of the blog is about the DAX functions. To go further, here is a SQL query to display the modification date of a SQL Server table.
Be the first to comment