Data and Index sizes in your user tables

Last Post 16 Dec 2010 03:38 PM by Vedran Kesegic. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Jeff Armstrong
New Member
New Member

--
12 Sep 2007 03:37 PM
I've used this script a few times just to get an idea of the size of my tables ect. Save your results each time you run it (once a month) to easily monitor table growth. It's a simple script, but handy.




CREATE TABLE #tmpSpaceUsed
( [NAME] VarChar(255) NULL
,[rows] Int Null
,[reserved] VarChar(255) Null
,[data] VarChar(255) Null
,[index_size] VarCHar(255) Null
,[unused] VarCHar(255)
)


SET NOCOUNT ON

DECLARE @vchTableName VarChar(255)
DECLARE c1 INSENSITIVE CURSOR
FOR SELECT [name] FROM sysobjects
WHERE TYPE = 'U'

OPEN C1
FETCH NEXT FROM C1 INTO @vchTableName

WHILE (@@FETCH_STATUS = 0)
BEGIN
set @vchTablename = @vchTableName
INSERT INTO #tmpSpaceUsed
EXEC sp_SpaceUsed @vchTableName
FETCH NEXT FROM C1 INTO @vchTableName
END
CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF

SELECT [NAME]
,[ROWS]
,Convert(INT,LEFT(reserved,CHARINDEX(' ',reserved))) AS [RESERVED]
,Convert(INT,LEFT(data,CHARINDEX(' ',data))) AS [DATA]
,Convert(INT,LEFT(index_size,CHARINDEX(' ',index_size))) AS [INDEX_SIZE]
,CASE WHEN IsNull([ROWS],0) = 0 THEN 0 ELSE Convert(INT,LEFT(data,CHARINDEX(' ',data))) / [ROWS]
END AS [Average K/row]
FROM #tmpSpaceUsed
ORDER BY [Average K/row] desc --Convert(INT,LEFT(data,CHARINDEX(' ',data))) DESC

DROP TABLE #tmpSpaceUsed
SQLUSA
New Member
New Member

--
22 Sep 2007 02:49 PM
I tried your script. It is really GOOD!

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandprix
prospec
New Member
New Member

--
24 Oct 2007 09:29 AM
Good script, thanks dude.
Vedran Kesegic
New Member
New Member

--
16 Dec 2010 03:38 PM
Thank you for your script.
If you want to take a look at a single table size and it's indexes, you can use this simple plugin: XDetails http://www.sqlxdetails.com


Acceptable Use Policy
---