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:
/* - Create a table to hold results - for sorting.
- Sadly a table variable won't do... so it has to
be a full-blown temp table (no biggie).
- See BooksOnline sp_spaceused for details on columns */
CREATE TABLE #sizes (
[name] nvarchar(60), -- BOL says (20), but that obviously wrong
[rows] char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
/* use the undocumented sp_MSforeachtable special
proceedure to dump out size info for each table in
your database. */
EXECUTE sp_MSforeachtable
@command1=" Print ''INSERT INTO #sizes EXEC sp_spaceused ''"
SELECT * FROM #sizes ORDER BY CAST(REPLACE(data,' KB','') AS int) DESC
--DROP TABLE #sizes-- Run this line when you're done looking at stuff...
This code could easily be slapped into a sproc... though I prefer to use it as a script when I run it...
could that be modified to show table owner info as well? thanks, pm
Posted by: pm | June 21, 2005 at 01:10 PM
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
Posted by: Michael K. Campbell | June 21, 2005 at 01:36 PM
thanks! much appreciated...
Posted by: pm | June 21, 2005 at 01:49 PM
A cleaner version of this would be to run the foreachtable sproc call with the following command:
EXECUTE sp_MSforeachtable
@command1 = 'INSERT INTO #sizes EXEC sp_spaceused ''?'''
(which is more stable and easier to read)
Posted by: Michael K. Campbell | December 23, 2005 at 11:55 AM