APSQL

Obtenir la liste des plus tables les plus volumineuses

Depuis l'outils SQL Server Management Studio, il est relativement facile de connaitre l'espace disque occupé par chaque table. En effet il suffit de demander l'affichage du rapport Utilisation du disque par tables principales. Ce rapport est accesible depuis SQL Server Management Studio (SQL 2008) en sélectionnant la base concernée par cette analyse puis en sélectionnant Rapports - Rapports standards depuis le menu contextuel associé à la base.

En complément de cette vue graphique il est parfois nécessaire d'obtenir la liste textuelle des plus grosses tables de la bases. Pour établir cette liste l'option choisie ici conciste à définir une fonction qui retourne les informations de tailles de chaque table utilisateur de la base. Pour définir cette fonction, il est possible de s'appuyer sur la procédure stockée sp_spaceused. Pour obtenir le code de cette procédure, il suffit de sélectionner l'option Modifier depuis le menu contextuel associé à cette procédure.

obtenir le code de la procédure sp_spaceused

A partir du code source de la procédure, il est aisé d'isoler les informations concernant le problème à résoudre afin de définir la fonction espaceTable comme illustré ci dessous.

create function espaceTable ()
     returns @estimationEspace table (nomTable sysname, taille bigint, pagesUtilises bigint, lignes bigint) AS
BEGIN
     declare @dbname sysname;    -- nom de la base courante
     declare @object_id int;     -- identifiant interne des tables, ...
     declare @pages bigint;
     declare @usedpages bigint;
     declare @rowcount bigint;   
     declare @tableSchema sysname;
     declare @tableName sysname;
     declare cLesTables cursor for 
         select object_id from sys.objects where type in('U');
     -------> Nom de la base courante
     select @dbname=db_name();
     -------> Parcourir toutes les tables de la base courante
     open cLesTables;
     fetch cLesTables into @object_id;
     while @@fetch_status=0 begin
         -- calculer les informations relatives à la table
         select   @usedpages =sum(used_page_count),
                   @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),
                   @rowcount=sum(case 
                                      when (index_id<2) then row_count
                                      else 0
                                  end)
              from sys.dm_db_partition_stats
              where object_id=@object_id;
         -- Ajouter les informations à la table résultante
         insert into @estimationEspace(nomTable, taille, pagesUtilises, lignes)
              values(object_name(@object_id), @pages*8, @usedpages, @rowcount);
         -- Analyser la table suivante
         fetch cLesTables into @object_id;
     end;
     close cLesTables;
     deallocate cLesTables;
     return;
END;
			

Il est alors extrement facil d'établir la liste des n tables les plus volumineuses, comme l'illustre la requête présente ci dessous qui dresse la liste des 10 tables les plus volumineuses.

select top 10 * from dbo.espaceTable() order by 2 DESC;