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