Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
FORUM

Trouble with SQL output to a file

SQL output to file as batch job

FORUM

Trouble with SQL output to a file

SQL output to file as batch job

ARTICLE

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

FORUM

How to get Batch file error Message

Batch file

ARTICLE

The OUPUT Command

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

Tags
 
Contribute