Find size of all tables in a database

,

Sometimes it might be difficult for find size of all tables since it involves calling sp_spaceused stored procedure against each table. This is still worse when the number of tables are huge. The attached script lists all tables with number of rows, reserved, actual data size, index size and unused space in a database.

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_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 * From @v_TableSize Order By rows Desc
Go

Rate

4 (6)

Share

Share

Rate

4 (6)