Elencare in una query Sql le tabelle per spazio occupato su disco

Problema del giorno: preso un backup di un database di produzione, una volta fatto il restore sulla mia istanza di Sql Server volevo cancellare tutti i dati non interessanti per l’analisi che dovevo fare. Trattandosi di un database con un numero spropositato di tabelle, volevo concentrare la mia attenzione solo su quelle che mi avrebbero consentito di ottenere un reale guadagno, in termini di spazio occupato. Si trattava quindi di ordinare le tabelle nel database in base al loro spazio occupato.

Ci viene in soccorso la vista di sistema sys.allocation_units; quest’ultima riporta le dimensioni (espresse in termini di “pagine”) di ciascuna unità di allocazione associata alle tabelle. Il legame viene instaurato tramite la vista sys.partitions, che abbiamo già incontrato nella puntata precedente.

Come il commento iniziale sembra suggerire, lo script non è effettivamente farina del mio sacco, ma trovato bello fatto su stackoverflow:

/*
    Author : marc_s
    Date : 2011.11.25
    http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database
*/

SELECT
    TableName = tables.NAME,
    RowCounts = partitions.rows,
    TotalSpaceKB = SUM( allocation_units.total_pages ) * 8,
    UsedSpaceKB = SUM( allocation_units.used_pages ) * 8,
  UnusedSpaceKB = ( SUM( allocation_units.total_pages ) - SUM( allocation_units.used_pages ) ) * 8
FROM sys.tables
INNER JOIN sys.indexes
     ON indexes.object_id = tables.OBJECT_ID
    AND indexes.OBJECT_ID > 255
INNER JOIN sys.partitions
     ON indexes.object_id = partitions.OBJECT_ID
    AND indexes.index_id = partitions.index_id
INNER JOIN sys.allocation_units
     ON allocation_units.container_id = partitions.partition_id
WHERE tables.NAME NOT LIKE 'dt%'
   AND tables.is_ms_shipped = 0
GROUP BY tables.Name, partitions.Rows
ORDER BY tables.Name

Questo script è stato da me solo leggermente modificato per comprendere anche lo schema, escludere la tabella dbo.sysdiagrams (che pur essendo creata da Sql Server, ha il flag is_ms_shipped impostato a 0) ed infine per ordinare i dati secondo lo spazio usato (decrescente)

SELECT
    [Schema] = schemas.name,
    [Table] = tables.name,
    [RowCounts] = partitions.rows,
    [TotalSpaceKB] = SUM( allocation_units.total_pages ) * 8,
    [UsedSpaceKB] = SUM( allocation_units.used_pages ) * 8,
    [UnusedSpaceKB] = ( SUM( allocation_units.total_pages ) - SUM( allocation_units.used_pages ) ) * 8
FROM sys.tables
INNER JOIN sys.schemas
     ON schemas.schema_id = tables.schema_id
INNER JOIN sys.indexes
     ON indexes.object_id = tables.object_id
    AND indexes.object_id > 255
INNER JOIN sys.partitions
     ON indexes.object_id = partitions.object_id
    AND indexes.index_id = partitions.index_id
INNER JOIN sys.allocation_units
     ON allocation_units.container_id = partitions.partition_id
WHERE tables.name != 'sysdiagrams'
  AND tables.is_ms_shipped = 0
GROUP BY schemas.name, tables.Name, partitions.Rows
ORDER BY [UsedSpaceKB] DESC

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*