|
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
|
