SQL to view the size of all tables

Copy codeThe Code is as follows:
Declare @ id int
Declare @ type character (2)
Declare @ pages int
Declare @ dbname sysname
Declare @ dbsize dec (15, 0)
Declare @ bytesperpage dec (15, 0)
Declare @ pagesperMB dec (15, 0)

Create table # spt_space
(
[Objid] int null,
[Rows] int null,
[Reserved] dec (15) null,
[Data] dec (15) null,
[Indexp] dec (15) null,
[Unused] dec (15) null
)

Set nocount on

-- Create a cursor to loop through the user tables

Declare c_tables cursor
Select id from sysobjects where xtype = 'U'

Open c_tables fetch next from c_tables into @ id

While @ fetch_status = 0
Begin
/* Code from sp_spaceused */
Insert into # spt_space (objid, reserved)
Select objid = @ id, sum (reserved)
From sysindexes
Where indid in (0, 1,255) and id = @ id


Select @ pages = sum (dpages)
From sysindexes
Where indid <2
And id = @ id

Select @ pages = @ pages + isnull (sum (used), 0)
From sysindexes
Where indid = 255 and id = @ id

Update # spt_space set data = @ pages
Where objid = @ id

/* 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
Where objid = @ id

/* 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
)
Where objid = @ id

Update # spt_space set [rows] = I. [rows]
From sysindexes I
Where I. indid <2 and I. id = @ id and objid = @ id

Fetch next from c_tables into @ id
End

Select TableName = (select left (name, 60) from sysobjects where id = objid ),
[Rows] = convert (char (11), rows ),
ReservedKB = ltrim (str (reserved * d. low/1024.,) + ''+ 'kb '),
DataKB = ltrim (str (data * d. low/1024 ..,) + ''+ 'kb '),
IndexSizeKB = ltrim (str (indexp * d. low/1024.,) + ''+ 'kb '),
UnusedKB = ltrim (str (unused * d. low/1024.,) + ''+ 'kb ')
From # spt_space, master. dbo. spt_values d
Where d. number = 1
And d. type = 'E'

Order by reserved desc

Drop table # spt_space
Close c_tables
Deallocate c_tables