Table Size

The following script will help you identifying the size of all the tables within current database in Kilobytes along with row numbers, index size and unused space.
Please make sure when you copy and paste to your management studio on the quotes.

Create Table #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))

SET NOCOUNT ON
insert #temp exec sp_msforeachtable ‘sp_spaceused ”?”’

select a.table_name,
a.row_count,
count(*) as col_count,
a.data_size as DATA,
a.reserved_size as RESERVED,
a.index_size as INDEX_SIZE,
a.unused_size as UNUSED
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by
a.table_name, a.row_count, a.data_size, a.reserved_size,a.index_size,a.unused_size
Order by CAST(Replace(a.data_size, ‘ KB’, ”) as integer) desc
drop table #temp

Advertisements

1 Comment »

  1. […] March 25, 2008 at 4:00 pm · Filed under SQL Server ·Tagged data, export, file, shrink file, shrink log, SQL Server, sqlserver, text Table Size […]

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: