List MS SQL Table Sizes for All Tables in a Database

by demtron on Saturday, June 06, 2009 01:56 PM

For a client's site that uses MS SQL Server, we needed a quick and easy way to see the space occupied by all tables in the database.  We found this great script here that does exactly that: 

 

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc
declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END
update @Space set data = replace(data, 'KB', '')
update @Space set data = convert(decimal(8,2), data)/1000
update @Space set data = data + 'MB'
update @Space set reserved = replace(reserved, 'KB', '')
update @Space set reserved = convert(decimal(8,2), reserved)/1000
update @Space set reserved = reserved + 'MB'
select * from @Space order by convert(decimal(8,2), replace(data, 'MB', '')) desc

 

 


Comments

Comments are closed

Powered by BlogEngine.NET 1.5.1.18
Theme by Mads Kristensen · Adapted by Demtron

Bookmark and Share

Calendar

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar
Log in

Milwaukee SEO Company

Milwaukee Access Programmer/Developer

Milwaukee Website Designer and Developer



Marketing / SEO

Blog Directory
blogarama - the blog directory
TopOfBlogs
Milwaukee area SEO, SEM, ASP.Net