Home > Scripts > Qual è la tabella più grande nel DB corrente?

Qual è la tabella più grande nel DB corrente?

Può succedere di avere la necessità di sapere quale sia la tabella più grande (in termini di spazio occupato), nel database su cui si sta lavorando.

Ci sono diversi modi per rispondere a questa domanda, si potrebbe utilizzare il report standard Disk Usage by Top Table fornito da SQL Server Management Studio (SSMS) oppure, se si volesse effettuare l’operazione attraverso T-SQL, si potrebbe eseguire la stored procedure di sistema sp_spaceused per ogni tabella del DB, salvando tutti i risultati parziali in una tabella temporanea per poi ordinarli in funzione della colonna “reserved” o “data”.

L’utilizzo della stored procedure sp_spaceused ci costringe però ad accettare una soluzione row-by-row, si dovrà effettuare una chiamata per ogni tabella del DB. All’aumentare delle tabelle, aumenterà (in modo direttamente proporzionale) il numero delle chiamate alla stored procedure.

Una soluzione alternativa è rappresentata dalla seguente Common Table Expression (CTE), basata sul codice interno della stored procedure sp_spaceused, ma che permette di ottenere il risultato voluto con una sola esecuzione:

WITH spaceused AS
(
  SELECT
    sys.dm_db_partition_stats.object_id
    ,reservedpages = SUM(reserved_page_count)
    ,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
    ,usedpages = SUM(used_page_count)
    ,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
    ,pages = SUM(CASE
                   WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                   ELSE lob_used_page_count + row_overflow_used_page_count
                 END
                )
    ,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
  FROM
    sys.dm_db_partition_stats
  JOIN
    sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
  OUTER APPLY
    (SELECT
       reserved_page_count AS it_reserved_page_count
       ,used_page_count AS it_used_page_count
     FROM
       sys.internal_tables AS it
     WHERE
     it.parent_id = object_id
     AND it.internal_type IN (202,204,211,212,213,214,215,216)
     AND object_id = it.object_id
  ) AS its
  WHERE
    sys.objects.type IN ('U', 'V')
  GROUP BY
    sys.dm_db_partition_stats.object_id
)
SELECT
  name = OBJECT_NAME (object_id)
  ,rows = convert (char(11), row_Count)
  ,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
  ,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
  ,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
  ,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
  ,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
  ,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
  ,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
FROM
  spaceused
ORDER BY
  pages DESC;
GO
Vi state chiedendo come sia stato possibile accedere al codice interno della stored procedure di sistema sp_spaceused? Semplice, è stato possibile attraverso la stored procedure sp_helptext 🙂

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

sp_alter_column – La stored procedure per modificare una colonna!

Sarà successo anche a voi di dover modificare il tipo di dato di una colonna …