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


J'ai hérité d'une base de données SQL Server assez volumineuse. Il semble prendre plus de place que je ne le pensais, compte tenu des données qu'il contient.

Y at-il un moyen facile de déterminer combien d'espace sur le disque chaque table consomme?


896
2017-10-25 16:14


origine


Réponses:


SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    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 
    t.Name

1883
2017-10-25 16:16



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

  1. Faites un clic droit sur la base de données
  2. Aller 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/fr-fr/library/bb510680.aspx


402
2018-04-24 13:46



sp_spaceused peut vous fournir des informations sur l'espace disque utilisé par une table, une vue indexée ou toute la base de données.

Par exemple:

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

Cela signale les informations d'utilisation du disque pour la table ContactInfo.

Pour l'utiliser pour toutes les tables à la fois:

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

Vous pouvez également obtenir l'utilisation du disque à partir de la fonctionnalité de rapports standard de SQL Server. Pour accéder à ce rapport, naviguez depuis l'objet serveur dans l'Explorateur d'objets, descendez dans l'objet Databases, puis cliquez avec le bouton droit sur n'importe quelle base de données. Dans le menu qui s'affiche, sélectionnez Rapports, puis Rapports standard, puis "Utilisation du disque par partition: [DatabaseName]".


78
2017-10-25 16:24



 exec  sp_spaceused N'dbo.MyTable'

Pour toutes les tables, utilisez .. (en ajoutant des commentaires de Paul)

exec sp_MSForEachTable 'exec sp_spaceused [?]'

26
2017-10-25 16:17



Après quelques recherches, je n'ai pas trouvé un moyen facile d'obtenir des informations sur toutes les tables. Il existe une procédure stockée pratique nommée sp_spaceused qui retournera tout l'espace utilisé par la base de données. S'il est fourni avec un nom de table, il renvoie l'espace utilisé par cette table. Toutefois, les résultats renvoyés par la procédure stockée ne peuvent pas être triés, car les colonnes sont des valeurs de caractères.

Le script suivant va générer les informations que je cherche.

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

25
2017-10-25 16:17



Voici une autre méthode: en utilisant SQL Server Management Studio, dans Object Explorer, allez dans votre base de données et sélectionnez les tables

enter image description here

Puis ouvrez le Détails Explorateur d'objet (soit en appuyant sur F7 ou aller à Afficher-> Détails de l'Explorateur d'objets). Dans la page des détails de l'explorateur d'objets, faites un clic droit sur l'en-tête de colonne et activez les colonnes que vous souhaitez voir dans la page. Vous pouvez également trier les données par colonne.

enter image description here


17
2018-01-18 17:11



Les requêtes ci-dessus sont utiles pour trouver la quantité d'espace utilisée par la table (index inclus), mais si vous voulez comparer l'espace utilisé par les index sur la table, utilisez cette requête:

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
    sys.indexes AS i JOIN 
    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN 
    sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id

13
2017-07-11 18:36



Si vous avez besoin de calculer exactement les mêmes numéros, qui sont sur la page 'propriétés de la table - stockage' dans SSMS, vous devez les compter avec la même méthode que dans SSMS (fonctionne pour sql server 2005 et supérieur ... et aussi fonctionne correctement pour les tables avec des champs LOB - car il suffit de compter "used_pages" pour afficher une taille d'index précise:

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.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) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

9
2018-01-31 03:39



-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''

INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1

SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC

5
2017-11-11 21:45



Un petit changement sur la réponse de Mar_c, puisque je reviens si souvent sur cette page, ordonnée par la première ligne:

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
    --p.rows DESC --Uncomment to order by amount rows instead of size in KB.
    SUM(a.total_pages) DESC 

5
2018-04-13 07:21