Technical FAQs

Ask a Question

How to determine the size of a SQL database table

Goals and Symptoms

Goal
Determine size of tables in a SQL database.

Facts and Changes

ION Enterprise 6.0, Database Space, Database Size, Table Size, Archive Trim, sp_spaceused

Causes and Fixes

To look at a single table in a Database
1. In the SQL Server Management Studio start a new query and change the name of the database (<dbname>) and table (<tablename>).

use<dbname>
execsp_spaceused'<tablename>'

2. Then press "Execute"

To look at all the tables in a Database
1. In the SQL Server Management Studio start a new query.
2. Paste the following script in the New Query Window and change the name of the database (<dbname>) in the first line.

use<dbname>
setnocounton

ifexists(select name from tempdb..sysobjectswhere name='##tmp')
droptable ##tmp
createtable ##tmp(nam varchar(50),rowsint, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
go

declare @tblname varchar(50)
declare tblname CURSORforselect name fromsysobjectswhere xtype='U'
open tblname
Fetchnextfrom tblname into @tblname

WHILE@@FETCH_STATUS= 0
BEGIN
insertinto ##tmp
execsp_spaceused@tblname
FETCHNEXTFROM tblname INTO @tblname
END

CLOSE tblname
deallocate tblname
go

select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##tmp
droptable ##tmp


3. Then press "Execute"

Was this helpful?
What can we do to improve the information ?