size of the table

Question

Post reply

size of the table

Krasavita

SSC-Insane

Points: 23138
More actions
June 1, 2010 at 10:44 am

#96816

How can I check sizes for my tables at one time? Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the procedure sp_spaceused takes an optional parameter, tablename, which would give you some info like this:

sp_spaceused CLIENTREQHIST

--results:

name rows reserved data index_size unused

CLIENTREQHIST 21398 4544 KB 3960 KB 576 KB 8 KB

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Is there way to find out for all tables at the same time?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

at its most basic -

select 'exec sp_spaceused ['+name+']' from sysobjects where type = 'U'

copy and paste resilts to another query window and run

---------------------------------------------------------------------

Ken Jr. SSCommitted Points: 1842 More actions · Answer 4

-- Create the temp table for further querying

CREATE TABLE #temp(

rec_idint IDENTITY (1, 1),

table_namevarchar(128),

nbr_of_rowsint,

data_spacedecimal(15,2),

index_spacedecimal(15,2),

total_sizedecimal(15,2),

percent_of_dbdecimal(15,12),

db_sizedecimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data

SELECT left(table_name,50)as Table_name, nbr_of_rows, (data_space / 1024) as data_space_MB, (index_space / 1024) as index_space_MB, (total_size / 1024) as total_size_MB, percent_of_db, (db_size / 1024) as db_space_used_MB

FROM #temp

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp