Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Analyze Space Used for each Table

By Jags2001,

The following batchfile demonstrates how to use Windows command extension to extract the values of sp_spaceused for each table in the database. The output of sp_spaceused itself contains strings that do not get easily analyzed. The batch file extracts the numbers and outputs the sizes for each table in the database.

Copy the batchfile to a file named gettablesizes.bat and run it at a DOS prompt. You will be asked to supply the connection parameters to your database. The output is a comma delimited file named %Server%_%dbname%_size.txt

The batch file introduces DBA's with advanced OSQL and NT or Windows 2000 command line techniques like FOR/F, CALL and SET.

Some techniques used here are:
1: Echo out a SQL script to a file
2: Use the output of osql as input to another osql command in  a loop
3: Parse an output of osql and format it using FOR /F and call to 'subroutines' in a batch file.

Give it a try! I would welcome comments about these techniques.

Total article views: 2250 | Views in the last 30 days: 9
Related Articles

Trouble with SQL output to a file

SQL output to file as batch job


Trouble with SQL output to a file

SQL output to file as batch job


A Technique for Determining the I/O Hog in your Database

Performance Tuning can be as much an art as a science when working with SQL Server, but there are ma...


How to get Batch file error Message

Batch file


The OUPUT Command

SQL Server 2005 has added many new T-SQL commands, one of which is the OUTPUT command. Longtime SQL ...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones