October 15, 2018 by Kenneth Fisher
- Indexed views
- Service Broker queues
It’s the first and last that I use the most often. It’s just an incredibly quick and easy way to get row count, size and free space within a table, and total size and free space within a database.
USE Test; GO EXEC sp_spaceused;
So my Test database is about 23GB in size, 20GB unallocated, 2GB of data, 23MB of indexes, and ~1MB allocated to objects but not actually in use.
Now, in the research for this post, I found that there is a flag that sets the output to a single rowset. I particularly liked that because I find the two rowsets annoying.
EXEC sp_spaceused 'Table1', @oneresultset = 1;
My Table1 table has 3 rows, is 72KB in size of which 8KB is data, 8KB is indexes and 56KB is unused. And best of all the output is in a single row.
Again, this is a really easy fast way to get size information about a table, database, indexed view or SB queue. That said, if you need all of the tables (or databases) then go with the built-in reports available, or the Object Explorer Details view, which I think is going to be my next post.