Friday, October 27, 2006

How to spot a big table

There are plenty of tools out in the wonderful world of the web to provide you with this very detail of information. Do you need it? Well Gui's can be fun and down right useful when in the right environment. In my world I use software to help keep track of my LIVE/Production systems but, what about those other systems that are not really live, but are development and test. Sometimes you start to run out of room, and you wonder why... sometimes you want a quick count of the tables that either consume the most space in terms of disk, or rows... How can you do it quickly?

The following snippet is not groundbreaking, but it does provide a valuable service. More importantly it answer the questions of... which table is bigger... which tables are unused. etc, etc. You can even take this to the next level and keep track of which tables grow the most by surveying all your databases. And if you really like GUIs you can graph a trend on which tables grow the most.. stuff that is already available by vendors for thousands a month.. not groundbreaking like I said earlier but certainly useful and a good exercise to practice. Once you have it you can save a ton of Money on the GUIs out there...

Below I took advantage of using the undocumented stored procedure sp_msforeachtable, and the global variable '?'. Essentially for each table in the database run this sproc (stored procedure).


--By: Francisco
--Date: 10/27/2006
--Purpose: Find Out wich Tables Yield the most space.
IF EXISTS (select * from TEMPDB.dbo.sysobjects WITH(NOLOCK) where id = object_id(N'TEMPDB.dbo.#SpaceUsed')) BEGIN
DROP TABLE #SpaceUsed
END
CREATE TABLE #SpaceUsed (TableName VARCHAR(255),Rows INT, Reserved Varchar(255), Data VarChar(255), Index_Size VarChar(255), Unused VarChar(255))
INSERT INTO #SpaceUsed (TableName, Rows, Reserved, Data, Index_Size, UnUsed)
EXEC sp_msforeachtable 'EXEC sp_spaceused ''?'''


SELECT TableName, Rows, Reserved, Data, Index_Size, Unused FROM #SpaceUsed
ORDER BY CAST(REPLACE (DATA, 'KB', '') AS INT) DESC