SQL Server – How big are my tables?

Code Snippet
create table #t
(
    name sysname primary key clustered,
    rows bigint,
    reserved sysname,
    data sysname,
    index_size sysname,
    unused sysname
);

exec sp_MSforeachtable 'insert into #t exec sp_spaceused ''?''';

select
    name,
    rows,
    convert( bigint, replace( reserved, ' kb', '' ) ) / 1024 as [reserved MB],
    convert( bigint, replace( data, ' kb', '' ) ) / 1024 as [data MB],
    convert( bigint, replace( index_size, ' kb', '' ) ) / 1024 as [index MB],
    convert( bigint, replace( unused, ' kb', '' ) ) / 1024 as [unused MB]
from
    #t
order by
    3 desc;

drop table #t;

Advertisements
This entry was posted in SQL Server and tagged . Bookmark the permalink.

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