[SQL Server] Trouver la taille des tables dans toute la BD
Declare @v_TableName Varchar(100)
Declare @v_Table Table (Table_Name Varchar(100))
Declare @v_TableSize Table (
Table_Name Varchar(100),
rows BigInt,
Reserved Varchar(50),
ActualDataSize Varchar(50),
IndexSize Varchar(50),
Unused Varchar(50)
)
Insert Into @v_Table
Select TABLE_SCHEMA + '.' + Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'
Select @v_TableName = Table_Name From @v_Table
While Exists(Select 1 From @v_Table)
Begin
Insert Into @v_TableSize exec sp_spaceused@v_TableName
Delete From @v_Table Where Table_Name = @v_TableName
Select @v_TableName= Table_Name From @v_Table
End
Select Table_Name ,
[rows] ,
CAST(SUBSTRING(reserved,0, LEN(reserved) -(LEN(reserved) - CHARINDEX('k',reserved,0))) AS NUMERIC(24,4)) Reserved_KB,
CAST(SUBSTRING(ActualDataSize,0, LEN(ActualDataSize) -(LEN(ActualDataSize) - CHARINDEX('k',ActualDataSize,0))) AS NUMERIC(24,4)) ActualDataSize_KB,
CAST(SUBSTRING(IndexSize,0, LEN(IndexSize) -(LEN(IndexSize) - CHARINDEX('k',IndexSize,0))) AS NUMERIC(24,4)) IndexSize_KB,
CAST(SUBSTRING(Unused ,0, LEN(Unused ) -(LEN(Unused ) - CHARINDEX('k',Unused ,0))) AS NUMERIC(24,4)) Unused_KB
From @v_TableSize Order By ActualDataSize_KB Desc