DIff between Max length of value in CHAR type Colu

,

This Script finds the maximum length of a value in Character type Column.(CHAR,VARCHAR,NVARCHAR) and finds the difference between the Total lenghth and maximum value inserted

BEGIN

SET ANSI_WARNINGS OFF

Declare @tab_name varchar(50)
declare @co_name varchar(50)
declare  cursor1 cursor for 
--SELECT table_name,column_name from system tables for columns of type CHAR,NCHAR,VARCHAR,NVARCHAR of Columns having name 'NO' and 'NUMBER'

Select  '['+ y.name +']' as Table_name  , x.name as Column_name From syscolumns x
inner join   sysobjects y on x.id = y.id
where x.xtype  in ('231' , '239','167','175') and y.xtype ='U' and (x.name like '%NUMBER' OR X.NAME LIKE '%NO')

        OPEN  CURSOR1
FETCH NEXT from cursor1 into @tab_name,@co_name
                PRINT '    THE DIFF BETWEEN COLUMN WIDTH AND MAXIMUM VALUE IN FOLLOWING COLUMNS IS LESS THAN 4 '
PRINT '----------------------------------------------------------------------------------------------'
       While @@fetch_status = 0
          begin
                        --EXECUTING DYNAMIC SQL TO GET THE MAXIMUM LENGTH OF VALUE IN BYTES FOR A COLUMN 
DECLARE @SQLString NVARCHAR(500)
                DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @MYVALUE INT
                                DECLARE @DIFFVALUE INT 

                 SET @SQLString ='SELECT @MYVALUEOUT =  max(datalength('+'['+@co_name+']'+'))from '+@tab_name 
                 
SET @ParmDefinition = '@MYVALUEOUT INT OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@MYVALUEOUT = @MYVALUE OUTPUT
 
--EXECUTING DYNAMIC SQL TO GET THE TOTAL LENGTH IN BYTES OF A COLUMN 

                DECLARE @SQLString1 NVARCHAR(500)
                DECLARE @ParmDefinition1 NVARCHAR(500)
DECLARE @MYVALUE1 INT

                set @SQLString1 ='select @MYVALUEOUT1 = COL_LENGTH('+''''+@tab_name+''''+','+''''+@co_name+''''+')' 
SET @ParmDefinition1 = '@MYVALUEOUT1 INT OUTPUT'
EXECUTE sp_executesql
@SQLString1,
@ParmDefinition1,
@MYVALUEOUT1 = @MYVALUE1 OUTPUT


        --DIFFERENCE OF TOTAL LENGHT AND MAXIMUM VALUE LENGHTH IN BYTES 
                SET @DIFFVALUE = (@MYVALUE1 - @MYVALUE)
IF @DIFFVALUE <= 4 
BEGIN
DECLARE @PRINTSTRING VARCHAR(500)
  SET @PRINTSTRING = @CO_NAME +SPACE(2)+ 'COLUMN'+SPACE(2)+'IN TABLE'+SPACE(3)+rtrim(@TAB_NAME)+SPACE(3)+'DIFFERENCE IS' +SPACE(2)+ CAST(@DIFFVALUE AS VARCHAR)
--SET @PRINTSTRING = 'THE '+ rtrim(@CO_NAME)+SPACE(5)+ 'COLUMN'+SPACE(5)+cast(@MYVALUE1 AS varchar) +'W'+ SPACE(2)+CAST(@MYVALUE AS varCHAR) +'V '+SPACE(5)+'IN TABLE ' +rtrim(@TAB_NAME) 
--SET @PRINTSTRING = 'THE '+ rtrim(@CO_NAME)+ SPACE(5)+ cast(@MYVALUE1 AS varchar) +' '+ CAST(@MYVALUE AS varCHAR) +' '+' COLUMN  IN TABLE ' +rtrim(@TAB_NAME) + 'IS LESS THAN 4 CHARACTER FROM ITS ORIGIONAL LENGHTH'
          
PRINT @PRINTSTRING
END

                SET @MYVALUE = 0
SET @MYVALUE1 =0
   SET @DIFFVALUE =0          
           FETCH NEXT from CURSOR1 into @tab_name,@co_name
           END
CLOSE CURSOR1
DEALLOCATE  CURSOR1
SET ANSI_WARNINGS ON
END


--ONE CAN VERIFY  THE VALUE RETURNED BY THIS SCRIPT BY FOLLOWING SQL COMMANDS

--IN FOLLOWING EXAMPLE  << TYPES_ARA >> IS TABLE NAME .  <<TYPEID>> IS COLUMN_NAME 
--SELECT COL_LENGTH('Types_ara','TypeId')
--SELECT MAX(datalength(ltrim(rtrim(TypeId)))) from Types_ara

Rate

Share

Share

Rate