November 13, 2003

Shared SQL Server Hosting - Table Sizes

SQL Server Enterprise Manager has some nice features. One that I make a good deal of use of is the TaskPad View when looking at a database. It allows me to quickly spot how much space is being used in my datafiles and logfiles, and it also allows me to go through and see how much space my tables are taking up. It's not the perfect tool for in depth analysis, but it's good for a quick overview.

Sadly, if you host sites/databases in a shared hosting environment you can run into issues with oodles of databases clogging Enterprise Manager (thanks to the fact that the DMO has to enumerate EACH database on the server before it can display them in the databases node). In other words... waiting for literaly 12 minutes to get to your database node and switch it to TaskPad view can be a hassle. For cases like that, and cases where you may not have access to Enterprise Manager, or in cases where you'd like to order your tables by size, etc I've created the following code snippet:

Comments

Sure. The key to doing that is just to grab the userid from the name of the table. There's a killer SQL Server function: OBJECTPROPERTY that will do it for you. Once that's done, just join against that value to a suitable table (sysusers) and you're good to go.
Replace the SELECT * FROM ... (down near the bottom) with this:
SELECT u.[name] [owner],*
FROM #sizes s
INNER JOIN dbo.sysusers u ON u.uid = OBJECTPROPERTY(OBJECT_ID(s.[name]),'OwnerId')
ORDER BY CAST(REPLACE(s.data,' KB','') AS int) DESC