To see this information, you need to view the size of the individual tables. Thankfully, SQL Server has a built in stored procedure, sp_SpaceUsed, which displays the storage statistics of individual tables. Leveraging this stored procedure, we have created a batch script which allows you to easily produce a listing of each table in a database and view its storage statistics.
When the script is run, the following information for each table in the database is listed in a tabular format:
Database table name Number of rows in the table Total disk space allocated to this table by SQL Amount of disk space used for data storage Amount of disk space used for internal SQL indexes Amount of disk space currently unused
Using the Script
The DBSize batch script is compatible with SQL 2005 and higher and must be run on a machine which has the SQLCMD tool installed (installed as part of the SQL Server installation). It is recommended you drop this script into a location set in your Windows PATH variable (i.e. C:Windows) so it can easily be called like any other application from the command line.
To view the help information, simply enter:
Examples
To run a report on “MyDB” on the default instance and direct the output to “MyDB Table Size.txt” on the desktop:
To run a report on “MyDB” on the named instance “Special” using the “sa” user with password “123456”:
Download the Database Table Size Batch Script from SysadminGeek.com
