Estimate table size using command extensions

This batch file is used to calculate the size used by user tables and is very useful in big databases. The file creates a comma-delimited output file that can be imported into a spreadsheet or another table for further analysis--like determining the top 10% of tables.

I used to create a cursor from the sysobjects table and loop through each table with sp_spaceused in Query Analyzer. I would then export the output to Excel, parse it and analyze it.

sp_spaceused 'dbo.authors' 
name        rows        reserved     data        index_size    unused
---------   ---------   ---------    ---------   ----------    ---------
authors     23          48 KB        8 KB        40 KB         0 KB

With the batch file, the extra step is eliminated and you have a ready file for analysis. Download the code and name the batch file Download the code and name the file gettablesizes.bat. The syntax is:

gettablesizes user password server database 

Example:

gettablesizes sa pw MyServer pubs 

The output file is %Server%_%database%_size.txt. Example: MyServer_pubs_size.txt. The batch file demonstrates the power of both OSQL and Windows command extensions.

1. It creates a script by echoing out to a file tables.sql. The script queries the sysobjects tables for user tables. It identifies the table owner and table names. This is really nice if your tables are owned by another user other than DBO.

2. The script is then run in osql creating an output tbllist.txt. Notice the "-h-1" and "-s," parameters. This means that the output will have no headers and each output row is comma delimited.

3. The table.txt is then read using the FOR /F "tokens=1,2 delims=," to read the owner and table name.

4. The owner and table name is passed to osql for "sp_spaceused". Example:

sp_spaceused 'dbo.authors'

The output of sp_spaceused is then passed to a file named table.txt.

5. table.txt is read removing the KB from sp_spaceused, which is then appended to the output file. Notice the command extension call, " call :showsize " and passing the parameters read from sp_spaceused.

This is only about 60 lines of code (with lots of rems), but what power! DBA's should familiarize themselves with OSQL and how it can work with Windows command extensions.

@echo off
rem gettablesizes.bat 
rem  Outputs the tables sizes into a comma delimited file 

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

set osqlsetting=-U%user% -S%server% -d%dbname% -n -h-1 -s, -w200
osql %osqlsetting% -itables.sql -otbllist.txt

rem header
ECHO owner,name,rows,reserved,data,index_size,unused >%output%    
FOR /F   "tokens=1,2 delims=," %%i in (tbllist.txt) DO (
  osql %osqlsetting% -Q"exec sp_spaceused '%%i.%%j'" >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
  )  
)

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

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.