Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Re-purpose my TableSize Script

Last Year I introduced a couple of scripts that I worked on.  Those scripts developed into a series covering the comparison of some methods and MS provided stored procs that could help you in the retrieval of table sizes in SQL Server.

I pulled out one of those scripts recently in order to find what tables, in an R and D database, were consuming alot of space.  While running that script, I realized that it could easily be used to help me with an additional task.  I decided that this script could help me deduce the top 10 biggest tables in a database that doesn’t necessarily need to have that data.  Or maybe, I just need to clean out the data so I can test populating the database.  This script is predicated on a lack of foreign keys – but can easily be adapted.

So, in all of its glory:

/*similar to sp_spaceused */
 
DECLARE @dbsize DECIMAL(19,2)
		,@logsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		, @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
/*
**  We want all objects.
*/
BEGIN
	WITH FirstPass AS (
		SELECT OBJECT_ID,
			ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
			UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024,
			PageCnt = SUM(
			CONVERT(DECIMAL(19,2),CASE
				WHEN (index_id < 2)
					THEN (used_page_count)
				ELSE lob_used_page_count + row_overflow_used_page_count
				END
			)) * 8/1024,
			RowCnt = SUM(
			CASE
				WHEN (index_id < 2)
					THEN row_count
				ELSE 0
			END
			)
		FROM sys.dm_db_partition_stats
		--Where OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
		GROUP BY OBJECT_ID
	)
	,InternalTables AS (
		SELECT ps.OBJECT_ID,
			ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) * 8/1024,
			UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) *8/1024
		FROM sys.dm_db_partition_stats  ps
			INNER Join sys.internal_tables it
				ON it.OBJECT_ID = ps.OBJECT_ID
				And it.internal_type IN (202,204,211,212,213,214,215,216)
		WHERE it.parent_id = ps.OBJECT_ID
			--And OBJECTPROPERTY(ps.OBJECT_ID,'IsMSShipped') = 0
		GROUP BY ps.OBJECT_ID
	)
SELECT 'Truncate table ' + OBJECT_NAME (f.OBJECT_ID) + '' AS TruncTabStatement
			,TableSizeMB = SUM(f.PageCnt) +
					SUM(CASE WHEN (f.UsedPage + IsNull(i.UsedPage,0)) > f.PageCnt
						THEN ((f.UsedPage + IsNull(i.UsedPage,0)) - f.PageCnt) ELSE 0 END)
	FROM FirstPass F
		LEFT Outer Join InternalTables i
			ON i.OBJECT_ID = f.OBJECT_ID
	WHERE OBJECTPROPERTY(f.OBJECT_ID,'IsMsShipped') = 0
	GROUP BY f.OBJECT_ID
	ORDER BY TableSizeMB DESC
 
END

I think the script, along with prior explanations, is pretty straight forward.  This can quickly help reset those LARGE tables for continued testing.  Of course, that is predicated on you not already having a script for that, and that you don’t know what tables need to be reset (maybe you are in a large team).

This is just one example of a script that can be useful for more than what it was first designed to do.  As DB professionals, we often come across situations were a prior script can easily be repurposed for the task at hand.  Knowing that, and how to do it, is an important tool in your toolbox.

Comments

Posted by Dukagjin Maloku on 4 March 2011

Jason nice explanation & thanks for sharing!

Posted by Jason Brimhall on 4 March 2011

Thanks - and you are welcome

Leave a Comment

Please register or log in to leave a comment.