Determine size of data in tables
Sample script that can be used to determine the size of data in a set of tables in a SQL database./*
The following SQL script can be used to determine the size of data in
a set of tables in a SQL 2005 database.
The script makes use of the system stored procedure, sp_spaceused.
*/
-- declare a local table variable
declare @tblObjSize as table
(
Name varchar(255)
, Rows int
, Reserved varchar(50)
, Data varchar(50)
, index_size varchar(50)
, unused varchar(50)
, lReserved int default(0)
, lData int default(0)
)
-- declare & open a cursor to iterate through each of our tables in the database.
-- Uncomment and modify the 'where' clause in the select statement to
-- return the necessary tables.
DECLARE curTables cursor FAST_FORWARD for
select name from sys.tables
--where name like 'ag_%'
order by name
OPEN curTables
-- declare a variable to hold the table name retrieved from the cursor
DECLARE @sObjName nvarchar(776)
-- get the 1st row of data from the cursor
FETCH next from curTables into @sObjName
-- turn off the record insert counter
SET nocount on
-- keep going while tha last fetch was good.
WHILE (@@fetch_status = 0)
begin
-- run the system stored proc to get the space used for the current table.
INSERT into @tblObjSize ( Name, Rows, Reserved, Data, index_size, unused )
exec sp_spaceused @sObjName
-- get the next row of data from the cursor
FETCH next from curTables into @sObjName
end
-- turn on the record counter
SET nocount off
-- close & deallocate the cursor
CLOSE curTables
DEALLOCATE curTables
-- Update the lReserved & lData columns, removing the 'KB' and converting to int.
UPDATE @tblObjSize set
lReserved = convert(int, substring(Reserved, 0, len(Reserved) - 2))
, lData = convert(int, substring(Data, 0, len(Data) - 2))
-- output our results.
SELECT *
from @tblObjSize
order by lData desc
declare @lReserved int, @lData int
SELECT @lReserved = sum(lReserved), @lData = sum(lData) from @tblObjSize
print 'Total Reserved: ' + convert(varchar, @lReserved) + ' KB'
print 'Total Data: ' + convert(varchar, @lData) + ' KB'
The output will look something like this:
Name | Rows | Reserved | Data | index_size | unused | lReserved | lData |
AG_Person_MSTR | 1552092 | 2720408 KB | 1552096 KB | 1167800 KB | 512 KB | 272040 | 155209 |
AG_Person_Address_MSTR_CASS | 1506728 | 901400 KB | 901120 KB | 24 KB | 256 KB | 90140 | 90112 |
AG_Person_Address_MSTR | 1508741 | 505480 KB | 502920 KB | 2464 KB | 96 KB | 50548 | 50292 |
AG_PersonAttribute_MSTR | 1921490 | 546328 KB | 427000 KB | 119160 KB | 168 KB | 54632 | 42700 |
AG_Registrant_WORK | 821701 | 1080152 KB | 331240 KB | 594272 KB | 154640 KB | 108015 | 33124 |
Last modified by Mohit @ 4/4/2025 5:13:28 PM