1

I have a simple query that returns every table, index and the columns of the index in a database. I'd like to add two aggregate columns that return the number of indexes on a table and then the amount of columns on each index.

I'm looking to build a report of every database on a server, each table in there and each index and column to look for tables with too many indexes and large indexes with more than 2-3 key columns.

 SELECT 
 @@SERVERNAME as server_name
,DB_NAME() as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,ColumnName = col.name
 ,ic.key_ordinal
 ,ind.is_disabled
 ,IndexId = ind.index_id
 ,ColumnId = ic.index_column_id
 FROM 
 sys.indexes ind 
 INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and 
 ind.index_id = ic.index_id 
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and 
  ic.column_id = col.column_id 
  INNER JOIN sys.tables t ON ind.object_id = t.object_id 
  WHERE t.is_ms_shipped = 0 
  ORDER BY database_name, t.name, ind.name, ind.index_id

2 Answers 2

3

If you want a stable, reliable, and free script, try sp_BlitzIndex. As a disclaimer, it's part of an open source project I contribute to.

If you run it like so, it will go through all the databases on your server and produce the information you asked for, plus a lot of other nice details.

EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2;

0
2

Something like this?

 SELECT 
 @@SERVERNAME as server_name
,DB_NAME() as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,IndexId = ind.index_id
 ,sum(case when key_ordinal = 0 then 0 else 1 end ) as amount_of_key_columns
 , count(ind.name) over(partition by t.name) as indexes_on_table
 FROM 
 sys.indexes ind 
 INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and 
 ind.index_id = ic.index_id 
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and 
  ic.column_id = col.column_id 
  INNER JOIN sys.tables t ON ind.object_id = t.object_id 
  WHERE t.is_ms_shipped = 0 
    group by t.name,ind.name, ind.index_id

Example of a Resultset: (one record for each index)

server_name database_name   TableName   IndexName   IndexId amount_of_key_columns   indexes_on_table
SRVSQLA1      my_test      tabela1     AKF_tabela1_Kod  3          1    3
SRVSQLA1      my_test      tabela1     BK_tabela1       1          1    3
SRVSQLA1      my_test      tabela1     PK_tabela1       2          1    3

To do it for all the databases in a server:

DECLARE @sql NVARCHAR(max);
DECLARE C CURSOR for SELECT NAME FROM sys.databases WHERE database_id > 4 and state = 0;
DECLARE @dbname NVARCHAR(255);
OPEN C 
FETCH NEXT FROM C INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '

USE '+QUOTENAME(@dbname)+'

 SELECT 
 @@SERVERNAME as server_name
 ,'''+QUOTENAME(@dbname)+''' as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,IndexId = ind.index_id
 ,sum(case when key_ordinal = 0 then 0 else 1 end ) as amount_of_key_columns
 , count(ind.name) over(partition by t.name) as indexes_on_table
 FROM 
 sys.indexes ind 
 INNER JOIN sys.index_columns ic
 ON  ind.object_id = ic.object_id 
 and ind.index_id = ic.index_id 
 INNER JOIN sys.columns col 
 ON ic.object_id = col.object_id 
 and ic.column_id = col.column_id 
 INNER JOIN sys.tables t 
 ON ind.object_id = t.object_id 
 WHERE t.is_ms_shipped = 0 
 GROUP by t.name,ind.name, ind.index_id;';

EXEC(@sql);
FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;
2
  • 1
    Thx, only issue i'm having is that it counts the rows where the key_ordinal is 0. Playing with it to get around it. Worst case I might create a CTE for this part.
    – Alen
    Dec 12, 2018 at 20:36
  • Ow yeah i made a mistake, now only the key columns should be counted Dec 12, 2018 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.