Obtain SQL Server 2000 database and tablespace usage information

Reference Source: http://sqlforums.windowsitpro.com/web/forum/messageview.aspx? Catid = 74 & threadid = 47900 & enterthread = y

When using a database, we sometimes need to know the disk space occupied by the database. In the past, the disk space usage of each table in the database is as follows:

1. Database disk space usage informationSp_spaceused

Table 2 disk space usage informationSp_spaceused'Table Name'

3. Obtain disk space usage information for all tables in the database. Create   Proc Spaceused_simulator
@ Database_name   Varchar ( 128 )
As  
Declare   @ Cmd   Varchar ( 1000 ),
@ Bytes   Int  

Set Nocount On  


Select   @ Bytes   =   [ Low ] / 1024   From Master .. spt_values
Where   Number   =   1  
And Type =   ' E '  

Create   Table # Tmp_spaceused (
ID Int   Null ,
Tablename Varchar ( 128 ) Null ,
Rows Int   Null ,
Reserved Int   Null ,
Data Int   Null ,
Index_size Int   Null ,
Unused Int   Null )

Set   @ Cmd   =  
' Insert into # tmp_spaceused (ID, tablename)
Select ID, name from '   +   @ Database_name   +   ' .. Sysobjects
Where xtype = '' U ''  
And name <> '' Dtproperties '''  

Exec ( @ Cmd )


Set   @ Cmd   =  
' Update # tmp_spaceused
Set rows = A. Rows
From '   +   @ Database_name   +   ' .. Sysindexes
Where # tmp_spaceused.id = A. ID
And a. indid in (0, 1) '  

Exec ( @ Cmd )


Set   @ Cmd   =  
' Update # tmp_spaceused
Set reserved = A. sumreserved
From (select ID, sum (Reserved) as sumreserved
From '   +   @ Database_name   +   ' .. Sysindexes
Where indid in (0, 1,255)
Group by ID) as
Join # tmp_spaceused on A. ID = # tmp_spaceused.id '  

Exec ( @ Cmd )


Set   @ Cmd   =  
' Update # tmp_spaceused
Set Data = C. Data
From (select a. ID, A. sumdpages + isnull (B. sumused, 0) as data
From (select ID, sum (dpages) as sumdpages
From '   +   @ Database_name   +   ' .. Sysindexes
Where indid in (0, 1)
Group by ID) as
Left join
(Select ID, isnull (sum (used), 0) as sumused
From '   +   @ Database_name   +   ' .. Sysindexes
Where indid = 1, 255
Group by ID) as B
On a. ID = B. ID) as C
Join # tmp_spaceused on C. ID = # tmp_spaceused.id '  

Exec ( @ Cmd )


Set   @ Cmd   =  
' Update # tmp_spaceused
Set index_size = A. sumused-# tmp_spaceused.data
From (select ID, sum (used) as sumused
From '   +   @ Database_name   +   ' .. Sysindexes
Where indid in (0, 1,255)
Group by ID) as
Join # tmp_spaceused on A. ID = # tmp_spaceused.id '  

Exec ( @ Cmd )


Set   @ Cmd   =  
' Update # tmp_spaceused
Set unused = # tmp_spaceused.reserved-A. sumused
From (select ID, sum (used) as sumused
From '   +   @ Database_name   +   ' .. Sysindexes
Where indid in (0, 1,255)
Group by ID) as
Join # tmp_spaceused on A. ID = # tmp_spaceused.id '  

Exec ( @ Cmd )


Update # Tmp_spaceused
Set  
Reserved = Reserved *   @ Bytes ,
Data = Data *   @ Bytes ,
Index_size = Index_size *   @ Bytes ,
Unused = Unused *   @ Bytes  


Select   *   From # Tmp_spaceused Order   By Tablename

Go