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