Retrieving Space Information For All Tables

                                
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

                                
table.

 

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

              
                   
allocations.

                                 
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         

                                 
image data.

 

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         

                                 
image data.

 

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’
As
Begin
Set nocount on
Declare @tablename varchar(128), @type varchar(12), @space_allocated int,
@no_of_records int, @data_space_used int, @index_space_used int,
@freespace int, @pages int

If @runupdate not in (‘true’,’false’)

begin

Raiserror (‘Incorrect values, the valid values are true
or false’, 16,1)
return
end

If (@runupdate=’TRUE’)
Begin
dbcc updateusage(0) with no_infomsgs
–Print ‘DBCC Updateusage completed’
–Print ‘Retrieving space information for ‘+db_name() + ‘ database…’
End

 Create table #temp1
(table_name varchar(30),
 
table_type varchar(15),
 
no_of_records varchar(20),
 
space_allocated varchar(20),
 
data_space_used varchar(20),
 
index_space_used varchar(20),
 
freespace varchar(20))

 

Create table #temp2
(
tablename varchar(128),
tabletype varchar(12))

Insert into #temp2
Select [name], case when type=’S’ then ‘System Table’ else ‘User Table’ end
From sysobjects where type=’U’ or type=’S’

Declare c1 cursor for
Select tablename, tabletype from #temp2

Open c1
Fetch next from c1 into @tablename, @type
While (@@fetch_status=0)
Begin

–space_allocated

Select @space_allocated = sum (reserved)*8 from
sysindexes
Where indid in (0, 1, 255) and id =object_id (@tablename)

–rows

Select  @no_of_records
= a. rows from sysindexes a
Where a.indid < 2 and a.id = object_id (@tablename)

–data_space_used

Select @pages = sum (dpages) from sysindexes
Where indid < 2 and id = object_id (@tablename)
Select @pages = @pages + isnull (sum (used), 0) from sysindexes
Where indid = 255 and id = object_id (@tablename)
Set @data_space_used = @pages*8

–index_space_used
Select @index_space_used =(select sum (used) from sysindexes
Where indid in (0, 1, 255) and id = object_id (@tablename))*8
Set @[email protected]_space_used – @data_space_used

 

–freespace

Select @freespace =(select sum (used) from sysindexes
Where indid in (0, 1, 255) and id = object_id (@tablename))*8
Set @[email protected][email protected]

Insert into #temp1
(table_name, table_type, no_of_records, space_allocated, data_space_used,
index_space_used, freespace)
Values (@tablename,@type, convert (varchar(15),@no_of_records)+’ rows’,
convert (varchar(15), @space_allocated) + ‘KB’, convert(varchar(15),@data_space_used)
+ ‘KB’, convert(varchar(15),@index_space_used) + ‘KB’,convert(varchar(15),@freespace
)+ ‘KB’)

Fetch next from c1 into @tablename, @type
End

Select * from #temp1
Close c1
Deallocate c1
Set nocount off
End

                              
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 protected]

 

Latest Articles