Obtenir la taille de toutes les tables de la base de données

Question

J'ai hérité d'une base de données SQL Server assez importante. Elle semble prendre plus d'espace que prévu, compte tenu des données qu'elle contient.

Existe-t-il un moyen simple de déterminer l'espace disque consommé par chaque table ?

Answer 1

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Answer 2

Si vous utilisez SQL Server Management Studio (SSMS), au lieu d'exécuter une requête ( qui dans mon cas a retourné des lignes en double ), vous pouvez exécuter un rapport type . 

1. Cliquez à droite sur la base de données
2. Naviguez vers Rapports > Rapports standard > Utilisation du disque par table

Remarque : le niveau de compatibilité de la base de données doit être défini sur 90 ou plus pour que cela fonctionne correctement. Voir http://msdn.microsoft.com/en-gb/library/bb510680.aspx

Answer 3

Sp_spaceused peut vous donner des informations sur l'espace disque utilisé par une table, une vue indexée ou la base de données entière.

Par exemple :

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

Ce rapport présente les informations sur l'utilisation du disque pour la table ContactInfo.

Pour l'utiliser pour toutes les tables en même temps :

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

Vous pouvez également obtenir l'utilisation du disque à partir de la fonctionnalité Rapports standard du serveur SQL, accessible par clic droit. Pour accéder à ce rapport, naviguez à partir de l'objet serveur dans l'Explorateur d'objets, descendez jusqu'à l'objet Bases de données, puis cliquez avec le bouton droit de la souris sur une base de données. Dans le menu qui apparaît, sélectionnez Reports, puis Standard Reports, et enfin "Disk Usage by Partition : [DatabaseName]".

Answer 4

 exec  sp_spaceused N'dbo.MyTable'

Pour tous les tableaux ,utiliser..(en ajoutant les commentaires de Paul)

exec sp_MSForEachTable 'exec sp_spaceused [?]'

Answer 5

Après quelques recherches, je n'ai pas trouvé de moyen facile d'obtenir des informations sur tous les tableaux. Il existe une procédure stockée pratique appelée sp_spaceused qui renvoie tout l'espace utilisé par la base de données. Si on lui fournit un nom de table, elle renvoie l'espace utilisé par cette table. Cependant, les résultats renvoyés par la procédure stockée ne sont pas triables, car les colonnes sont des valeurs de caractères.

Le script suivant générera les informations que je recherche.

create table #TableSize (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #ConvertedSizes (
    Name varchar(255),
    [rows] int,
    reservedKb int,
    dataKb int,
    reservedIndexSize int,
    reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows], 
SUBSTRING(reserved, 0, LEN(reserved)-2), 
SUBSTRING(data, 0, LEN(data)-2), 
SUBSTRING(index_size, 0, LEN(index_size)-2), 
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
order by reservedKb desc

drop table #TableSize
drop table #ConvertedSizes