datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to list all table sizes in a database?

Execute the following Microsoft SQL Server T-SQL scripts to list the database and each table size within the AdventureWorks2008 database. If you need to store size information for long-term tracking of database growth, permanent table can be used instead of @tablevariable.

use AdventureWorks2008;

go

 

-- Space used by AdventureWorks2008 database

exec sp_spaceused

go

 

/* Results

database_name     database_size     unallocated space

AdventureWorks2008      182.06 MB   0.00 MB

 

reserved    data  index_size  unused

184608 KB   96112 KB    82064 KB    6432 KB

*/

 

-- Space used for all tables

declare @TableSpace table (TableName sysname, RowsK varchar(32),

      ReservedMB varchar(32), DataMB varchar(32),

      IndexSizeMB varchar(32), UnusedMB varchar(32))

     

insert @TableSpace

exec sp_MSforeachtable @command1="exec sp_spaceused '?';"

 

update @TableSpace set RowsK = CONVERT(varchar,

      1+convert(int, RowsK)/1024)

update @TableSpace set ReservedMB = CONVERT(varchar,

      1+convert(int,LEFT(ReservedMB, charindex(' K', ReservedMB,-1)))/1024)

update @TableSpace set DataMB = CONVERT(varchar,

      1+convert(int,LEFT(DataMB, charindex(' K', DataMB,-1)))/1024)

update @TableSpace set IndexSizeMB = CONVERT(varchar,

      convert(int,LEFT(IndexSizeMB, charindex(' K', IndexSizeMB,-1)))/1024)

update @TableSpace set UnusedMB = CONVERT(varchar,

      convert(int,LEFT(UnusedMB, charindex(' K', UnusedMB,-1)))/1024)

     

select * from @TableSpace order by convert(int,DataMB) desc

go

Partial results:

TableName RowsK ReservedMB DataMB IndexSizeMB UnusedMB
Person 20 82 30 51 0
SalesOrderDetail 119 16 10 5 0
DatabaseLog 2 7 7 0 0
TransactionHistory 111 10 7 3 0

 

 

------------

-- List table sizes in descending order - undocumented system proc

------------

CREATE TABLE #TableSpaceUsed (

      name sysname,

      [rows] varchar(32),

      reserved varchar(32),

      data varchar(32),

      index_size varchar(32),

      unused varchar(32))

INSERT #TableSpaceUsed

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT * FROM #TableSpaceUsed

ORDER BY DATA DESC

/* name     rows  reserved    data  index_size  unused

SalesOrderDetail  121317     15752 KB    9880 KB     5312 KB     560 KB

CustomerAddress   19220      1616 KB     864 KB      552 KB      200 KB

Customer    19185      2600 KB     824 KB      1368 KB     408 KB */

------------

Related article:

Displaying the Sizes of Your SQL Server's Database's Tables By Scott Mitchell

 

Exam Prep 70-461
Exam 70-461