Not logged in - Login
< back

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