A Stored Procedure for viewing the space usage of the MSSQLServer database, SpaceUsed
A Stored Procedure for viewing the space usage of the MSSQLServer database, SpaceUsed
Run the following stored procedure
Then you can directly view it by using SpaceUsed.
Stored Procedure Code
Program code
The Code is as follows:
Create procedure SpaceUsed
As
Begin
Declare @ id int -- The object id of @ objname.
Declare @ type character (2) -- The object type.
Declare @ pages int -- Working variable for size calc.
Declare @ dbname sysname
Declare @ dbsize dec (15, 0)
Declare @ logsize dec (15)
Declare @ bytesperpage dec (15, 0)
Declare @ pagesperMB dec (15, 0)
Declare @ objname nvarchar (776) -- The object we want size on.
Declare @ updateusage varchar (5) -- Param. for specifying that
Create table # temp1
(
Table Name varchar (200) null,
Number of rows char (11) null,
Reserved space varchar (15) null,
Data Space varchar (15) null,
Index space varchar (15) null,
Unused space varchar (15) null
)
-- Select @ objname = 'n' _ dep '-- usage info. shocould be updated.
Select @ updateusage = 'false'
/* Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** Reserved: sum (reserved) where indid in (0, 1,255)
** Data: sum (dpages) where indid <2 + sum (used) where indid = 255 (text)
** Indexp: sum (used) where indid in (0, 1,255)-data
* Unused: sum (reserved)-sum (used) where indid in (0, 1,255)
*/
Declare cur_table cursor
Select name from sysobjects where type = 'U'
Open cur_table
Fetch next from cur_table into @ objname
While @ FETCH_STATUS = 0
Begin
Create table # spt_space
(
Rows int null,
Reserved dec (15) null,
Data dec (15) null,
Indexp dec (15) null,
Unused dec (15) null
)
/*
** Check to see if user wants usages updated.
*/
If @ updateusage is not null
Begin
Select @ updateusage = lower (@ updateusage)
If @ updateusage not in ('true', 'false ')
Begin
Raiserror (15143,-1,-1, @ updateusage)
Return (1)
End
End
/*
** Check to see that the objname is local.
*/
If @ objname IS NOT NULL
Begin
Select @ dbname = parsename (@ objname, 3)
If @ dbname is not null and @ dbname <> db_name ()
Begin
Raiserror (15250,-1,-1)
Return (1)
End
If @ dbname is null
Select @ dbname = db_name ()
/*
** Try to find the object.
*/
Select @ id = null
Select @ id = id, @ type = xtype
From sysobjects
Where id = object_id (@ objname)
/*
** Does the object exist?
*/
If @ id is null
Begin
Raiserror (15009,-1,-1, @ objname, @ dbname)
Return (1)
End
If not exists (select * from sysindexes
Where @ id = id and indid <2)
If @ type in ('P', 'D', 'R', 'tr', 'C', 'rf ') -- data stored in sysprocedures
Begin
Raiserror (15234,-1,-1)
Return (1)
End
Else if @ type = 'V' -- View => no physical data storage.
Begin
Raiserror (15235,-1,-1)
Return (1)
End
Else if @ type in ('pk', 'uq') -- no physical data storage .--?!?! Too extends similar messages
Begin
Raiserror (15064,-1,-1)
Return (1)
End
Else if @ type = 'F' -- FK => no physical data storage.
Begin
Raiserror (15275,-1,-1)
Return (1)
End
End
/*
** Update usages if user specified to do so.
*/
If @ updateusage = 'true'
Begin
If @ objname is null
Dbcc updateusage (0) with no_infomsgs
Else
Dbcc updateusage (0, @ objname) with no_infomsgs
Print''
End
Set nocount on
/*
** If @ id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @ Dbsize = Pages used
** @ Bytesperpage = d. low (where d = master. dbo. spt_values) is
** The # of bytes per page when d. type = 'E' and
** D. number = 1.
** Size = @ dbsize * d. low/(1048576 (OR 1 MB ))
*/
If @ id is null
Begin
Select @ dbsize = sum (convert (dec (15), size ))
From dbo. sysfiles
Where (status & 64 = 0)
Select @ logsize = sum (convert (dec (15), size ))
From dbo. sysfiles
Where (status & 64 <> 0)
Select @ bytesperpage = low
From master. dbo. spt_values
Where number = 1
And type = 'E'
Select @ pagesperMB = 1048576 // @ bytesperpage
Select database_name = db_name (),
Database_size =
Ltrim (str (@ dbsize + @ logsize)/@ pagesperMB, 15, 2) + 'mb '),
'Unallocated space' =
Ltrim (str (@ dbsize-
(Select sum (convert (dec (15), reserved ))
From sysindexes
Where indid in (0, 1,255)
) // @ PagesperMB,) + 'mb ')
Print''
/*
** Now calculate the summary data.
** Reserved: sum (reserved) where indid in (0, 1,255)
*/
Insert into # spt_space (reserved)
Select sum (convert (dec (15), reserved ))
From sysindexes
Where indid in (0, 1,255)
/*
** Data: sum (dpages) where indid <2
** + Sum (used) where indid = 255 (text)
*/
Select @ pages = sum (convert (dec (15), dpages ))
From sysindexes
Where indid <2
Select @ pages = @ pages + isnull (sum (convert (dec (15), used), 0)
From sysindexes
Where indid = 1, 255
Update # spt_space
Set data = @ pages
/* Index: sum (used) where indid in (0, 1,255)-data */
Update # spt_space
Set indexp = (select sum (convert (dec (15), used ))
From sysindexes
Where indid in (0, 1,255 ))
-Data
/* Unused: sum (reserved)-sum (used) where indid in (0, 1,255 )*/
Update # spt_space
Set unused = reserved
-(Select sum (convert (dec (15), used ))
From sysindexes
Where indid in (0, 1,255 ))
Select reserved = ltrim (str (reserved * d. low/1024 ..,) +
''+ 'Kb '),
Data = ltrim (str (data * d. low/1024 ..,) +
''+ 'Kb '),
Index_size = ltrim (str (indexp * d. low/1024 ..,) +
''+ 'Kb '),
Unused = ltrim (str (unused * d. low/1024 ..,) +
''+ 'Kb ')
From # spt_space, master. dbo. spt_values d
Where d. number = 1
And d. type = 'E'
End
/*
** We want a particle object.
*/
Else
Begin
/*
** Now calculate the summary data.
** Reserved: sum (reserved) where indid in (0, 1,255)
*/
Insert into # spt_space (reserved)
Select sum (reserved)
From sysindexes
Where indid in (0, 1,255)
And id = @ id
/*
** Data: sum (dpages) where indid <2
** + Sum (used) where indid = 255 (text)
*/
Select @ pages = sum (dpages)
From sysindexes
Where indid <2
And id = @ id
Select @ pages = @ pages + isnull (sum (used), 0)
From sysindexes
Where indid = 1, 255
And id = @ id
Update # spt_space
Set data = @ pages
/* Index: sum (used) where indid in (0, 1,255)-data */
Update # spt_space
Set indexp = (select sum (used)
From sysindexes
Where indid in (0, 1,255)
And id = @ id)
-Data
/* Unused: sum (reserved)-sum (used) where indid in (0, 1,255 )*/
Update # spt_space
Set unused = reserved
-(Select sum (used)
From sysindexes
Where indid in (0, 1,255)
And id = @ id)
Update # spt_space
Set rows = I. rows
From sysindexes I
Where I. indid <2
And I. id = @ id
Insert into # temp1
Select name = object_name (@ id ),
Rows = convert (char (11), rows ),
Reserved = ltrim (str (reserved * d. low/1024 ..,) +
''+ 'Kb '),
Data = ltrim (str (data * d. low/1024 ..,) +
''+ 'Kb '),
Index_size = ltrim (str (indexp * d. low/1024 ..,) +
''+ 'Kb '),
Unused = ltrim (str (unused * d. low/1024 ..,) +
''+ 'Kb ')
From # spt_space, master. dbo. spt_values d
Where d. number = 1
And d. type = 'E'
Drop table # spt_space
End
Fetch next from cur_table into @ objname
End
Close cur_table
DEALLOCATE cur_table
Select * from # temp1 order by len (data uses space) desc, data uses space desc, and the reserved space desc
Drop table # temp1
Return (0)
End
GO
