create  View Data_Size as

/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: Provides a way of viewing the size of tables, and what part of the table contribute to that size.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:

A number of columns have been added to provide more insight into the tables.

 

A K_Per_Row column is shown to give an idea as to how "dense" the inforamtion is.  A low value indicates that each new row adds very little to space.  This is useful when trying to identify where size savings can be made.

 

The number of Indexes is normally very tightly aligned with the K_Per_Row.  The more indexes on a table the more space each row adds (an additional record will mean more information in each and every index).

 

An additional column "Has_Clustered Index" is included as a general warning.  The Clustered Index of a table is how the data is stored on the physical disk.  A large table with no clustered index and no primary key is called a "Heap" and quickly becomes very fragmented since there is literally no logic as to how data is organised for this table on the disk.  A primary key is a proxy for a clustered index in that it acts as a clustered index when no clustered index exists.

*/
select
  sysobjects.name as Table_Name,
sum(cast(reserved as bigint)*8) as Total_Size ,
sum ( case when (indid =0 or indid=1) then cast(reserved as bigint)*8 else 0 end) as Data_and_Indexes,
sum ( case when (indid not in (255,0,1)) then cast(reserved as bigint)*8 else 0 end) as Index_Size,
sum ( case when (indid =0 or indid=1) then cast(reserved as bigint)*8 else 0 end) -
sum ( case when (indid not in (255,0,1)) then cast(reserved as bigint)*8 else 0 end)  as Data_Size,
sum ( case when (indid=255) then cast(reserved as bigint)*8 else 0 end) as Text_Size,
sum(case when indid in (0,1) then rowcnt else 0 end) as Rough_row_Count,
cast(sum(cast(reserved as bigint)*8)/ cast(case when sum(case when indid in (0,1) then rowcnt else 0 end) = 0 then 1 else sum(case when indid in (0,1) then rowcnt else 0 end) end as float) as numeric(12,2)) as [K_Per_Row],

sum(case when indid > 1 then 1 else 0 end) as Indexes,
case when isnull(sum(case when indid = 0 then 1 else 0 end),1) > 0 then 'No Clustered Index' else '' end  as Has_Clustered_Index
from dbo.sysindexes
join dbo.sysobjects on
sysindexes.id = sysobjects.id
where reserved <> 0 and sysobjects.xtype = 'U '
group by
  sysobjects.name
go
select * from Data_Size order by total_size desc