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