CREATE PROCEDURE USP_GET_LENGTH_EACH_ROW  @TABLE_NAME VARCHAR(100) = NULL
/*  
--##  
AUTHOR - RAKESH SHARMA  
Ver.1.0  
DESCRIPTION - PROCEDURE WILL LOOP THROUGH EACH OBJECT AND CALCULATE THE SIZE OF EACH ROW  
***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**  
INSTRUCTIONS:- HOW TO EXECUTE
	EXEC USP_GET_SIZE_EACH_ROW_1 (NO PARAMETER)
	DEFAULT PARAMETER =NULL --> WHICH MEANS THIS WILL LOOP THORUGH ALL THE TABLES AND CHECK SIZE OF EACH ROW
    
	PASSING TABLENAME TO GET THE SIZE OF EACH ROW
	EXEC USP_GET_SIZE_EACH_ROW_1 'TEST_COLUMN_STORE_INDEX'
*/  
AS  
SET NOCOUNT ON  
BEGIN  
DECLARE @table nvarchar(128)  
DECLARE @idcol int  
DECLARE @sql nvarchar(max)  
SET @idcol = 1  
DECLARE @COLUMN VARCHAR(200)  
IF @TABLE_NAME IS NULL
		BEGIN
						DECLARE emp_cursor CURSOR FOR       
						SELECT NAME  from SYS.SYSOBJECTS   WHERE TYPE='U'  
						OPEN emp_cursor      
						FETCH NEXT FROM emp_cursor       
						INTO @table      
						WHILE @@FETCH_STATUS = 0      
						BEGIN      
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table)  
						PRINT @TABLE  
						PRINT @COLUMN  
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@table)  
							set @sql = @sql + ') as ROWSIZE from ' + @table + ''  
						 EXEC (@sql)  
							FETCH NEXT FROM emp_cursor       
						INTO @table      
						END       
						CLOSE emp_cursor;      
						DEALLOCATE emp_cursor;      
			END
			ELSE
			BEGIN
						PRINT @TABLE_NAME
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME)  
						PRINT @TABLE_NAME  
						PRINT @COLUMN  
						
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@TABLE_NAME)  
							set @sql = @sql + ') as ROWSIZE from ' + @TABLE_NAME + ''  
						 EXEC (@sql)  
			END
END