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
