DIff between Max length of value in CHAR type Colu

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