Karl's Windows Admin Tips & Tricks

Aug 24 2010   12:04PM GMT

Determine a tables size in SQL.

Karl Gechlik Karl Gechlik Profile: Karl Gechlik

You might know how many lines of data you have in your table but do you actually know how much disk space the table is taking up? This was my issue this past weekend. I needed to know the aproximate size of a table in my production SQL database. I thought about it for a little while and then I emailed my database administrator and he gave me a command that made my life a little easier.

It turns out SQL has a stored procedure that will do exactly what I needed it to do. It will tell me the size of a table and its indexes.  The stored procedure is pretty easy to use and invoke. Check it out:

sp_spaceused ‘Tablename’

Just enter the above query into your SQL Query Analyzer and replace the ‘Tablename’ with your actual table’s name. You can see it in action against our Northwind database below:

You can see that 540 kb of space has been allocated for the table. There is 160 kb of data in the table and the indexes are the biggest space consumer using 320 kb of data.  It was very interesting to find out (even in my production database) that the indexes use more space than the actual data.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: