Analyze Space Used for each Table


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.

rem: gettablesizes.bat
rem  Outputs the tables sizes into a comma delimited file

@echo off
IF (%1)==() GOTO :USAGE
set user=%1
IF (%2)==() GOTO :USAGE
set OSQLPassword=%2
IF (%3)==() GOTO :USAGE
set server=%3
IF (%4)==() GOTO :USAGE
set dbname=%4
set output=%Server%_%dbname%_size.txt

echo set nocount on				>tables.sql
rem unremark the following if you want to ensure the latest 
rem echo DBCC UPDATEUSAGE ('%dbname%') WITH NO_INFOMSGS >>tables.sql
echo SELECT convert(varchar(3),user_name(uid)) >>tables.sql
echo   , convert(varchar(30),name)             >>tables.sql
echo FROM sysobjects 			       >>tables.sql
echo WHERE type='U'			       >>tables.sql
echo ORDER by user_name(uid),name	       >>tables.sql

rem create a comma delimited list, no headers
osql -U%user% -S%server% -d%dbname% -n -h-1 -s, -w300 -itables.sql -otbllist.txt

rem header for the output
ECHO owner,name,rows,reserved,data,index_size,unused >%output%    

rem loop through the tables and get the sizes using sp_spaceused
FOR /F   "tokens=1,2 delims=," %%i in (tbllist.txt) DO (
  osql -U%user% -S%server% -d%dbname% -h-1  -n -s, -w200 -Q"exec sp_spaceused '%%i.%%j', 'true'" >table.txt
  FOR /F "tokens=1,2,3,4,5,6 delims=,"  %%k  in (table.txt) DO call :showsize %%k %%l "%%m" "%%n" "%%o" "%%p" %%i

rem delete the temporary tables
del tables.sql
del table.txt
del tbllist.txt


set table=%1
set rows=%2
for /F "tokens=1 delims= " %%w in (%3) DO set reserved=%%w
for /F "tokens=1 delims= " %%x in (%4) DO set datasize=%%x
for /F "tokens=1 delims= " %%y in (%5) DO set indexsize=%%y
for /F "tokens=1 delims= " %%z in (%6) DO set unused=%%z
echo %7,%table%,%rows%,%reserved%,%datasize%,%indexsize%,%unused%
echo %7,%table%,%rows%,%reserved%,%datasize%,%indexsize%,%unused%    >>%output%
goto :EOF

Echo Parameters required.
echo Usage:
Echo    gettablesizes user password server database