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
SETLOCAL
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
GOTO :EOF
:showsize
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
:usage
Echo.
Echo Parameters required.
echo Usage:
Echo gettablesizes user password server database 
