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.
