Estrarre l’elenco degli indici di SQL Server: quali viste di sistema utilizzare

In questo articolo spieghiamo come estrarre l’elenco degli indici delle tabelle di un Database SQL Server Express 2019. Il codice non può tener conto di tutte le possibili combinazioni e feature di SQL Server, quindi non fare copia e incolla del codice senza i dovuti test.

Partiamo da sys.indexes e sys.objects

Il punto di partenza della nostra query sarà la vista di sistema di sistema sys.indexes. Se nell’istanza sono presenti più database, per prima cosa assicuriamoci di interrogare la vista del database corretto utilizzando il comando USE <nome database>. Partiamo subito con due osservazioni:

  • sono presenti molte righe relative a tabelle di sistema che non ci interessano;
  • la colonna index_id non assume valori univoci all’interno della vista. Ad esempio ci sono più righe con index_id = 1 ma differente valore in object_id.

Possiamo utilizzare tale colonna object_id per mettere in JOIN la sys.indexes con la sys.objects. Ora potremmo aggiungere un filtro sulla colonna type della sys.objects per considerare soltanto le tabelle definite dagli utenti. Scriveremo dunque:

SELECT 
  o.name AS tabella,
  i.name AS indice,
  i.type_desc,
  i.is_unique
FROM sys.indexes AS i
INNER JOIN sys.objects AS o 
  ON i.OBJECT_ID = o.OBJECT_ID
WHERE o.type = 'U';

All’interno di type_desc troviamo l’indicazione sulla tipologia di indice. La maggior parte delle volte potremo usare questa colonna per distinguere gli indice di tipo clustered (generalmente associati alla chiave primaria della tabella) da quelli non clustered. Tuttavia in alcuni Database potremmo trovare anche altri valori: ad esempio se sulla tabella non è presente un indice clustered, troveremo comunque una riga dove type_desc è valorizzato con “HEAP“, inoltre potrebbero essere presenti particolari tipologie di indici come quelli Columnstore (anche in questo caso divisi tra clustered e non clustered),  XML o geospaziali.

Con il filtro o.type=’U’ eliminiamo dall’output tutte le righe relative a task di sistema, infatti consideriamo soltanto gli indici su “tabelle definite dall’utente”. In realtà in questo modo filtriamo anche altre tipologie di indici particolari, come quelli costruiti su viste o su table-value function, che non tratteremo nell’articolo.

La colonna is_unique ci dice se l’indice è univoco e di conseguenza se ha anche un ruolo di vincolo sui dati inseribili nella tabella.

Video Corso T-SQL avanzato

Aggiungiamo le colonne tramite sys.index_columns

Per aggiungere le informazioni sulle colonne appartenenti all’indice possiamo eseguire due ulteriori join con la sys.index_columns (questa volta utilizzando sia l’object_id e sia l’index_id) e poi con la sys.columns per ottenere i nomi delle colonne.

SELECT 
  o.name AS tabella,
  i.name AS indice,
  i.type_desc,
  i.is_unique,
  c.name AS colonna,
  ic.key_ordinal AS posizione,
  ic.is_included_column 
FROM sys.indexes AS i 
INNER JOIN sys.objects AS o 
  ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.index_columns AS ic
  ON i.index_id = ic.index_id
     AND i.object_id = ic.object_id
INNER JOIN sys.columns AS c
  ON ic.object_id = c.object_id 
     AND ic.column_id = c.column_id
WHERE o.type = 'U'
ORDER BY
 tabella,
 indice,
 CASE WHEN 
   ic.is_included_column = 1 
   THEN 1 
   ELSE 0
 END,
posizione;

La colonna key_ordinal è molto utile per gli indici creati su più colonne in quanto va a specificare l’ordine utilizzato per la costruzione dell’indice. In alcuni casi è valorizzato con zero, ad esempio se la colonna è semplicemente inclusa tramite il comando INCLUDE, oppure nuovamente per indici particolari come i columnstore, xml o geospaziali. L’ORDER BY ci permette di vedere l’output con un ordinamento comodo.

Continua a imparare

Torna in alto
Torna su