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