ALTER PROCEDURE USP_GET_TABLE_SIZE_AVG_ROW_SIZE /* DESCRIPTION - PROCEDURE WILL CALCULATE AVG SIZE OF ROW SIZE ***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO ***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES** ***NOTE:- TEST THIS SCRIPT ON TEST MACHINE FIRST MODIFY ACCORDINGLY BEFORE EXECUTING ON PRODUCTION */ AS SET NOCOUNT ON BEGIN IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL DROP TABLE #SpaceUsed CREATE TABLE #SpaceUsed ( TableName sysname ,NumRows BIGINT ,ReservedSpace VARCHAR(50) ,DataSpace VARCHAR(50) ,IndexSize VARCHAR(50) ,UnusedSpace VARCHAR(50) ) DECLARE @str VARCHAR(500) SET @str = 'exec sp_spaceused ''?''' INSERT INTO #SpaceUsed EXEC sp_msforeachtable @command1=@str DECLARE @old_tname VARCHAR(100) DECLARE @avg_row INT DECLARE @tname VARCHAR(100) DECLARE @data INT DECLARE @row INT CREATE TABLE #size (tname VARCHAR(1024), row INT, reserve CHAR(18), data CHAR(18), index_size CHAR(18), unused CHAR(18)) CREATE TABLE #results (tname VARCHAR(100), data_size INT, rows INT) SELECT TOP 1 @tname=name FROM sysobjects where xtype = 'u' ORDER BY NAME SET @old_tname = '' WHILE @old_tname < @tname BEGIN INSERT INTO #size exec ('sp_spaceused ' + @tname + ',true') SELECT @row=row, @data=substring(data,1,CHARindex(' ',data)-1) from #size INSERT INTO #results values (@tname,@data,@row) SET @old_tname = @tname SELECT top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name end SELECT TableName, NumRows, CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB, CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB, CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB, CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB FROM #SpaceUsed ORDER BY ReservedSpace_MB desc SELECT UPPER(left(tname, 100)) AS TABLE_NAME, rows AS TOTAL_ROWS, (DATA_SIZE/1024) DATA_SIZE_MB, CASE rows WHEN 0 THEN 0 ELSE (1024*cast(data_size as float))/cast(rows as float) END as AVG_ROW_SIZE_KB, CASE rows WHEN 0 THEN 0 ELSE 8024.0/((1024*cast(data_size as float))/cast(rows as float)) END as NUMBER_ROWS_PER_PAGE FROM #results DROP TABLE #results DROP TABLE #size END