I have found a very good question discussion about how to find database physical size and table physical size using SQL Query. This query also returns number of rows and data size for each table in MB and GBs.
==================== Query ===============================
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005, 2008, R2
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB]
=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]
=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]
=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]
= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
create table #TABLE_SPACE_WORK
(
TABLE_NAME
sysname
not null ,
TABLE_ROWS
numeric(18,0)
not null ,
RESERVED
varchar(50)
not null ,
DATA
varchar(50)
not null ,
INDEX_SIZE
varchar(50)
not null ,
UNUSED
varchar(50)
not null ,
)
create table #TABLE_SPACE_USED
(
Seq
int
not null
identity(1,1)
primary key clustered,
TABLE_NAME
sysname
not null ,
TABLE_ROWS
numeric(18,0)
not null ,
RESERVED
varchar(50)
not null ,
DATA
varchar(50)
not null ,
INDEX_SIZE
varchar(50)
not null ,
UNUSED
varchar(50)
not null ,
)
create table #TABLE_SPACE
(
Seq
int
not null
identity(1,1)
primary key clustered,
TABLE_NAME
SYSNAME
not null ,
TABLE_ROWS
int
not null ,
RESERVED
int
not null ,
DATA
int
not null ,
INDEX_SIZE
int
not null ,
UNUSED
int
not null ,
USED_MB
numeric(18,4)
not null,
USED_GB
numeric(18,4)
not null,
AVERAGE_BYTES_PER_ROW
numeric(18,5)
null,
AVERAGE_DATA_BYTES_PER_ROW
numeric(18,5)
null,
AVERAGE_INDEX_BYTES_PER_ROW
numeric(18,5)
null,
AVERAGE_UNUSED_BYTES_PER_ROW
numeric(18,5)
null,
)
declare @fetch_status int
declare @proc
varchar(200)
select
@proc
= rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME
=
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE
= 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME
varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'
-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end
--While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB
=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB
=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW
=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW
=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW
=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW
=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED
=
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA
=
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE
=
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED
=
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go
drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
