size of the table

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

  • 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!

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

  • at its most basic -

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

    copy and paste resilts to another query window and run

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

  • -- 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

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

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