Generating report to view table sizes

[steveot]

I would like to run first a sql script to list all the tables in a database.

Then I would like to print out a list of table size.

I found sp_spaceused but that seems per table.

How can I create something that will list space use on all table is a simple command. I know I can view it in enterprise manager in task pad view but would like to dump it to a text file.

http://msdn2.microsoft.com/en-us/library/ms188776(SQL.90).aspx

 

[gmmastros]

Something like this?

Create Table #Temp(Name VarChar(300), Rows Integer, Reserved VarChar(100), Data VarChar(100), IndexSize VarChar(100), Unused Varchar(100))

exec sp_MSForEachTable '
Insert Into #Temp
exec sp_spaceUsed ''?'''

Select * from #Temp Order By name

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

here you go

CREATE TABLE #tempSize (TableName varchar(500),Rows int,Reserved varchar(99),
data varchar(99),IndexSize varchar(99),Unused varchar(99))

INSERT INTO #tempSize
Exec sp_msforeachtable 'EXEC sp_spaceused ''?'' '


SELECT * FROM #tempSize 
ORDER BY TableName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[steveot]

I guess my next quesion I have taken the script and created a dumpspace.sql file.

Running OSQL im issuing

osql -E -S Servername -dDatabaseName -idumpspace.sql

Question how to output it to a text file that looks pretty or formatted?

 

[gmmastros]

This may not be perfect, but...

osql -E -S Servername -dDatabaseName -idumpspace.sql -o"C:\Output.txt" -n



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom