Finding Meta-Data About Your SQL Database

A customer recently approached me with an urgent compliance request from their legal department: they needed to know, immediately, the size of their database stored in our servers, the number of tables existing inside that database, and the top five largest tables by volume (number of rows). This data has to come from several places, and it’s not immediately obvious where all of them are. I’m using a test database in this example for anonymity purposes, using Microsoft SQL Server 2008 R2 management tools to manage a SQL Server 2005 system.

The first, the size of the database, is easy. You can either just open the database properties window and read the raw data size from the dialog:

or read the data automatically using the built-in stored procedure sp_spaceused.

The second bit of information, the number of tables inside the database, can be obtained by writing a short query against the built-in information schema:

USE databaseName
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

And finally, the third data – the sizes of each table by volume – requires some custom processing to obtain. I found some information over at the SQLTeam which was very helpful. The BigTables.sql (locally mirrored here: BigTables.sql) script iterates through each table and returns the number of rows, sizes of the data, index and unused space.

I was able to satisfy our customer’s request within a few minutes of receiving it.

Advertisements
This entry was posted in Software, Technology and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s