dcsimg

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

                                 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 @index_space_used=@index_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 @freespace=@space_allocated-@freespace

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 l_muthu@hotmail.com.

 








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers