Retrieving Space Information For All Tables
January 21, 2001
This procedure allows you to retrieve space information of all the system tables and user tables in the current database. It makes use of the following attributes from the sysobjects and sysindexes table.
Sysobjects.id : contains the identification no. of the table.
Sysobjects.name : contains the name of the object (i.e. the table).
Sysobjects.type : contains the type of the object (i.e. user table, system
table, procedure, view, trigger, constraint etc)
Sysindexes.id : contains the id of the table which references the id of the Sysobjects
Sysindexes.indid : contains the id of the index that is between 0 and 255.
(Indid=0 is for a heap)
(Indid=1 is for a clustered index)
(Indid>1 is for a non-clustered index)
(Indid=255 is for a LOB data i.e. text, ntext or image)
Sysindexes.dpages : For indid>1, it contains the no. of index pages.
For indid=0 or 1, it contains the no. of data pages.
For indid=255, it is zero.
Sysindexes.reserved: After the first 8 pages are allocated to a table, all future allocations
are in units of 8 pages called extents. It is the value of sum of all the
For indid>1, it contains the no. of pages allocated for the index.
For indid=0 or 1, it contains the no. of pages allocated for all the
index and table data.
For indid=255, it contains the no. of pages allocated for text or
Sysindexes.used : For indid>1, it contains the no. of pages used for the index.
For indid=0 or 1, it contains the no. of pages used for all the
index and table data.
For indid=255, it contains the no. of pages used for text or
Sysindexes.rows : contains the no. of rows in the table for indid=0 or 1.
The script for the procedure is as follows:
Create proc spacedetails @runupdate varchar(5)='FALSE'
If @runupdate not in ('true','false')
Raiserror ('Incorrect values, the valid values are true
or false', 16,1)
Create table #temp1
Create table #temp2
Insert into #temp2
Declare c1 cursor for
Select @space_allocated = sum (reserved)*8 from
= a. rows from sysindexes a
Select @pages = sum (dpages) from sysindexes
Select @freespace =(select sum (used) from sysindexes
Insert into #temp1
Fetch next from c1 into @tablename, @type
Select * from #temp1
And, this procedure takes an optional parameter as "true" or "false". If the value true is passed then it issues DBCC UPDATEUSAGE on the current database else it skips this operation and retrieves the space information. Use this option when you think that the space information retrieved is not accurate. Certain operations such as select into will not always update the rows column in sysindexes table correctly.
So executing this procedure with a value of "true" forces the values in sysindexes table to be updated. However there will be some lag time between running this and looking at the contents of sysindexes. Running this procedure with a value of "true" against huge database can take a lot of time.
If you have any comments, concerns or suggestions regarding this you can mail me at email@example.com.