[SQL Server] Afficher la taille de toutes les tables et le groupe de fichiers de la table
DROP TABLE IF EXISTS #result
CREATE TABLE #result
(
DatabaseName NVARCHAR(128)
, SchemaName NVARCHAR(128) NULL
, TableName NVARCHAR(128) NULL
, is_memory_optimized bit NULL
, [RowCount] BIGINT NULL
, SizeInMB FLOAT NULL
, FileGroupName NVARCHAR(128) NULL
)
EXEC sp_MSforeachdb 'USE [?]; IF DB_NAME() NOT IN (''master'',''model'',''msdb''
,''tempdb'')
BEGIN INSERT INTO #result SELECT DB_NAME() AS DatabaseName, T2.name AS SchemaName
, T1.name AS TableName, T1.is_memory_optimized
, SUM(T4.rows) AS [RowCount], SUM(T5.total_pages) * 8.0 / 1024 AS SizeInMB
, T6.name AS FileGroupName
FROM sys.tables T1
LEFT JOIN sys.schemas T2 ON T1.schema_id = T2.schema_id
LEFT JOIN sys.indexes T3 ON T1.object_id = T3.object_id
LEFT JOIN sys.partitions T4 ON T3.object_id = T4.object_id AND T3.index_id = T4.index_id
LEFT JOIN sys.allocation_units T5 ON T4.partition_id = T5.container_id
LEFT JOIN sys.filegroups T6 ON T3.data_space_id = T6.data_space_id WHERE T3.type <= 1 GROUP BY T1.name,T1.is_memory_optimized, T2.name, T6.name; END; '
; SELECT * FROM #result
ORDER BY DatabaseName, TableName ASC;