sp_spaceused returns number of rows, disk space reserved, and disk space used by a table. However when you have to return the space used by all the tables in a database, you have two options: One is to loop through all the tables and then pass the table name to the sp_spaceused procedure. The second is to use the undocumented sp_MSforeachtable procedure. We will explore both of these over here:
The Lengthy WayUSE yourdbname
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
EXEC sp_spaceused @str
SET @I = @I +1
END
Note: The advantage in taking the lengthy approach is that you can create another temporary table and sort the tables based on the space used.
The Short WayUSE
yourdbnameEXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Note: sp_MSforeachtable is an undocumented stored procedure
References :http://msdn2.microsoft.com/en-us/library/ms188776.aspxhttp://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic37975.aspxhttp://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
About The Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook
5 comments:
Works great except for tables that are in a different schema. So join the sys.Schemas to get the qualified name and then you'll get all tables.. here's my modification
note: i also created another Table Var to store the results and then display them afterwards...
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @TableSizes AS TABLE
([TblName] VARCHAR(255), [NumRows] INT,
[Reserved_Size] VARCHAR(10), [Data_Size] VARCHAR(10), [Index_Size] VARCHAR(10), [Used] VARCHAR(10))
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT s.NAME +'.'+t.name FROM sys.Tables t
JOIN sys.Schemas s ON s.SCHEMA_ID = t.schema_id
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TableSizes
EXEC sp_spaceused @str
SET @I = @I +1
END
SELECT * FROM @TableSizes
Thanks Jeff!
Thanks for this - one issue I found was when running this against databases with very large tables (>10million), you would not get the results back due to the @TableSizes temporary table column widths being too small.
So i've made the temp table column widths bigger and also added a default sort order for the results.
Here's the updated version.
Thanks again.
/*
Script to show the sizes of the tables in the specfied database
*/
USE yourdbname
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @TableSizes AS TABLE
([TblName] VARCHAR(255), [NumRows] INT,
[Reserved_Size] VARCHAR(20), [Data_Size] VARCHAR(20), [Index_Size] VARCHAR(20), [Used] VARCHAR(20))
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT s.NAME +'.'+t.name FROM sys.Tables t
JOIN sys.Schemas s ON s.SCHEMA_ID = t.schema_id
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TableSizes
EXEC sp_spaceused @str
SET @I = @I +1
END
SELECT * FROM @TableSizes ORDER BY NumRows DESC
Thanks Simon for modifying the script to suit larger tables!
if you get the error 'convert ..'
for @TableSizes change it to [NumRows] VARCHAR(30) and get the result set in an excel and sort it..:)
Post a Comment