Table Size Estimator

,

This script will output an estimate for the space required for a given number of rows for a specified table; the estimated size is based on the data produced by the "sp_spaceused" stored procedure.

The script is written as stand alone, though it also lends itself to creation as a stored procedure, and can be run in any database where the user has the required permissions.

Full instructions on how to run the script are included in the script header.

The script has been tested on SQL Server 2000.

/******************************************************************
*
* 		SQL Server Disk Space Check
*
* Author : Dave Leathem
* Date   : 7th August 2003
* 
* This script displays an estimated size for a given table for a
* given number of rows. The calculation is based on the "data"
* value from the sp_spaceused stored procedure.
*
* The script can be set to display final estimates in KB, MB or GB.
* These results may be subject to small rounding errors.
*
* Required Permissions:
*	CREATE TABLE (in current db)
*	EXEC ON sp_spaceused
*
* Preset variables:
*	@table		Defines the name of the table to estimate.
*	@est_rows	Defines the number of rows to estimate.
*	@est_unit	Defines the units to display the estimated
*			size, can be 'KB', 'MB' or 'GB'.
*	
******************************************************************/

BEGIN

	/*****************************************
	* Declare and set preset variables
	*****************************************/
	DECLARE @table AS VARCHAR(50)
	DECLARE @est_rows AS INT
	DECLARE @est_unit AS CHAR(2)

	SET @table = 'sysobjects'
	SET @est_rows = 1
	SET @est_unit = 'KB' -- KB, MB or GB

	/*****************************************
	* Create temporary table for table size data
	*****************************************/
	CREATE TABLE #tspace
		([name]	VARCHAR(100),
		 [rows]	INT,
		 [rowsize] FLOAT,
		 [est_size] FLOAT,
		 [reserved] VARCHAR(50),
		 [data]	VARCHAR(50),
		 [index_size] VARCHAR(50),
		 [unused] VARCHAR(50))

	/*****************************************
	* Generate actual and estimated space
	*****************************************/
	-- Get current table space data
	INSERT INTO #tspace ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused @table

	-- Calculate average row size
	UPDATE #tspace
	SET [rowsize] = CAST(LEFT([data],CHARINDEX(' ',[data])) AS FLOAT)/[rows] 

	-- Calculate size for estimated number of rows
	IF @est_unit = 'MB'
		UPDATE #tspace SET [est_size] = ROUND(([rowsize] * @est_rows)/1024,2)
	ELSE IF @est_unit = 'GB'
		UPDATE #tspace SET [est_size] = ROUND((([rowsize] * @est_rows)/1024)/1024,2)
	ELSE
		UPDATE #tspace SET [est_size] = ROUND([rowsize] * @est_rows,2)

	/*****************************************
	* Output estimated size
	*****************************************/
	SELECT [Data], [Value] FROM
	(SELECT 7 AS [ord], 'Estimated Space' AS [Data], 
		CAST([est_size] AS VARCHAR) + ' ' + @est_unit AS [Value] FROM #tspace
	 UNION
	 SELECT 6 AS [ord], 'Estimated Row Count' AS [Data], 
		CAST(@est_rows AS VARCHAR) AS [Value] FROM #tspace
	 UNION
	 SELECT 5 AS [ord], 'Average Row Space' AS [Data], 
		CAST([rowsize] AS VARCHAR) AS [Value] FROM #tspace
	 UNION
	 SELECT 4 AS [ord], 'Reserved Space' AS [Data], 
		[reserved] AS [Value] FROM #tspace
	 UNION
	 SELECT 3 AS [ord], 'Current Actual Space' AS [Data], 
		[data] AS [Value] FROM #tspace
	 UNION
	 SELECT 2 AS [ord], 'Current Row Count' AS [Data], 
		CAST([rows] AS VARCHAR) AS [Value] FROM #tspace
	 UNION
	 SELECT 1 AS [ord], 'Table Name' AS [Data], 
		[name] AS [Value] FROM #tspace) a
	ORDER BY [ord]
	
	DROP TABLE #tspace

END

Rate

Share

Share

Rate