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.
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;