Analyze Space Used for each Table

Total article views: 1226 | Views in the last 30 days: 88

 Rate this |   Join the discussion |  Briefcase

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: 1226 | Views in the last 30 days: 88