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:
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 firstname.lastname@example.org 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.
This was first published in February 2002