Technical Article

Check Table Size and DB Size

,

 These scripts can be run straight on SSMS.

Check Table Sizes

* The <DB_NAME> should be repalced with the actual database name of the database for which table sizes are being checked.

* The script returns table names and  sizes for the database specified

Check Database Sizes

* The script returns the sizes of all data and log files for each database

* The sizes are represented in MB rather than 8KB pages

-- Check size of all tables in a database --
use ,DB_NAME>
go
create table #tablesize 
(name nvarchar(120),rows char(11),reserved varchar(18),
data varchar(18),index_size varchar(18),unused varchar(18))
go
insert into #tablesize
exec 
sp_msforeachtable @command1=
'exec sp_spaceused ''?'''
select * from #tablesize
go
drop table #tablesize
go

-- Check size of all databases in an instance --
use tempdb
go
create table #DatabaseSize 
(fileid int, groupid int, size int, maxsize int, growth int, status int,perf int,name varchar(50),filename varchar(100))
go
insert into #DatabaseSize
exec sp_msforeachdb @command1='select * from [?]..sysfiles;'
go
select name [DB File Name] ,filename [DB File Path],size*8/1024 [DB Size (MB)] from #DatabaseSize
go
drop table #DatabaseSize
go

Rate

3.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.2 (5)

You rated this post out of 5. Change rating