sp_spaceused - Comment mesurer la taille en GO dans toutes les tables dans SQL

Suivants de la base de la requête fonctionne. Il obtient le même résultat que sp_spaceused, en utilisant le même algorithme, mais beaucoup plus efficacement. S'il vous plaît ne pas utiliser le CURSOR + sp_spaceused méthode; il n'y a absolument aucune raison de le faire. Et un problème potentiel avec l'aide de sp_spaceused est qu'il est destiné à être un rapport proc donc la sortie est tout le texte, pas les chiffres réels, et l'analyse qu'en arrière dans les nombres peuvent être sujettes à l'erreur.

Il est également préférable de ne pas utiliser sys.tables ou sp_msforeachtable qu'ils excluent les vues indexées.

Ce qui suit est exactement le même que sp_spaceused en termes de:

• Inclut les index XML, FullText index, vues indexées, etc.
• Décompose l'info pour les Données vs Indice de l'espace utilisé

Si vous avez besoin de travailler pour toutes les bases de données, il peut être facilement adapté pour cela.

Si vous avez besoin de ces données ventilées par l'index, j'ai adapté la requête suivante en réponse à cette question sur DBA.StackExchange: l'utilisation de l'espace sur sys.allocation_units et sp_spaceused

;WITH extra AS
(   -- Get info for FullText indexes, XML Indexes, etc
SELECT  sit.[object_id],
sit.[parent_id],
ps.[index_id],
SUM(ps.reserved_page_count) AS [reserved_page_count],
SUM(ps.used_page_count) AS [used_page_count]
FROM    sys.dm_db_partition_stats ps
INNER JOIN  sys.internal_tables sit
ON  sit.[object_id] = ps.[object_id]
WHERE   sit.internal_type IN
(202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
GROUP BY    sit.[object_id],
sit.[parent_id],
ps.[index_id]
), agg AS
(   -- Get info for Tables, Indexed Views, etc (including "extra")
SELECT  ps.[object_id] AS [ObjectID],
ps.index_id AS [IndexID],
SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
SUM(ps.used_page_count) AS [UsedPageCount],
SUM(ps.reserved_page_count) AS [ReservedPageCount],
SUM(ps.row_count) AS [RowCount],
SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
AS [LobAndRowOverflowUsedPageCount]
FROM    sys.dm_db_partition_stats ps
GROUP BY    ps.[object_id],
ps.[index_id]
UNION ALL
SELECT  ex.[parent_id] AS [ObjectID],
ex.[object_id] AS [IndexID],
0 AS [InRowDataPageCount],
SUM(ex.used_page_count) AS [UsedPageCount],
SUM(ex.reserved_page_count) AS [ReservedPageCount],
0 AS [RowCount],
0 AS [LobAndRowOverflowUsedPageCount]
FROM    extra ex
GROUP BY    ex.[parent_id],
ex.[object_id]
), spaceused AS
(
SELECT  agg.[ObjectID],
OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
OBJECT_NAME(agg.[ObjectID]) AS [TableName],
SUM(CASE
WHEN (agg.IndexID < 2) THEN agg.[RowCount]
ELSE 0
END) AS [Rows],
SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
SUM(agg.LobAndRowOverflowUsedPageCount +
CASE
WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
ELSE 0
END) * 8 AS [DataKB],
SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
CASE
WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
ELSE 0
END) * 8 AS [IndexKB],
SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
OBJECT_SCHEMA_NAME(agg.[ObjectID]),
OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
sp.TableName,
sp.[Rows],
sp.ReservedKB,
(sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
sp.DataKB,
(sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
sp.IndexKB,
(sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
sp.UsedKB AS [UsedKB],
(sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
sp.UnusedKB,
(sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
so.[type_desc] AS [ObjectType],
so.[schema_id] AS [SchemaID],
sp.ObjectID
FROM   spaceused sp
INNER JOIN sys.all_objects so
ON so.[object_id] = sp.ObjectID
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
--ORDER BY ??

OriginalL'auteur Solomon Rutzky

Il ya un couple d'options ici:

sp_msforeachtable

sp_msforeachtable 'exec sp_spaceused [?]'

Tout sp_msforeachtable peut être utilisé pour cela est a quelques limitations:

• Tout d'abord, vous vous retrouvez avec un jeu de résultats pour chaque tableau est exécuté, ce qui est un dur travail avec
• Si vous l'exécutez sur de trop nombreuses tables alors vous avez frappé à la limite de la suite d'ensembles que SSMS s'appuyer (pense que c'est généralement autour de 200 marque)

Synthèse des résultats

Avec un peu plus de travail, nous pouvons rassembler tous les résultats en un seul ensemble de données, en évitant de ces limitations. Le motif de cette solution est similaire à chaque fois qu'il est nécessaire d'exécuter une procédure stockée un tas de fois avec des paramètres différents et de rassembler les résultats de ces pistes.

--Get list of interesting tables
declare @tables table(id int identity(1,1), name varchar(200))
declare @tablename varchar(200)
insert @tables
select table_name from information_schema.tables where table_type = 'BASE TABLE'
--Define table for results
declare @info table(name varchar(200), rows bigint, reserved varchar(200), data varchar(200), index_size varchar(200), unused varchar(200))
--Working vars
declare @max int, @pos int
select @max = count(1), @pos = 1 from @tables
--Loop to get all results
while @pos <= @max
begin
select @tablename = name from @tables where id = @pos
insert @info
exec sp_spaceused @tablename
set @pos = @pos + 1
end
--return all results
select * from @info

Je préfère utiliser des tableaux de variables pour cela, mais un curseur/temp table peut être utilisée aussi bien.

OriginalL'auteur Jon Egerton