SQL is a powerful tool that can be used to manage data. However, it can also be used to store data that is not related to the business. In a database, there are tables that are used for storage of data that is not related to the business. These tables are called individual SQL tables. The disk space usage for these tables is important because it affects the performance of the database. If these tables are not stored on a separate disk, then they will use up a lot of disk space and the database will be slow.
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
