CREATE PROCEDURE PreTest_19
	() LANGUAGE SQLSCRIPT 
AS 
	TableID nvarchar(100);
	AliasId nvarchar(100);
	EditSize int;
	SizeID int;
	RealSize int;
	SchemaSize int;
	
	statement nvarchar(5000);
	exists1 int;
	CURSOR alfa_udfs FOR
	SELECT T0."TableID", T0."AliasID", T0."EditSize",T0."SizeID" FROM CUFD T0
	INNER JOIN TABLE_COLUMNS T1 ON ('U_' || T0."AliasID") = T1.COLUMN_NAME AND T0."TableID" = T1.TABLE_NAME
	WHERE T1.DATA_TYPE_NAME = 'NVARCHAR' AND T1.SCHEMA_NAME = CURRENT_SCHEMA AND
	(T0."EditSize" != T1.LENGTH OR T0."SizeID" != T1.LENGTH OR
	T0."EditSize" > 254 OR T0."SizeID" > 254);

BEGIN
	OPEN alfa_udfs;
	FETCH alfa_udfs INTO TableID, AliasId, EditSize, SizeID;
	CREATE LOCAL TEMPORARY COLUMN TABLE #UDF_TEMP("TableName" nvarchar(50), "UDFName" nvarchar(100), "RealSize" int, "SchemaSize" int, "DefinedSize" smallint);
	CREATE LOCAL TEMPORARY COLUMN TABLE #SCHEMASIZE_TMP("SchemaSizeOut" int);
	WHILE NOT alfa_udfs::NOTFOUND DO
		SELECT COUNT(*) INTO exists1 FROM "PUBLIC".m_tables WHERE table_name = :TableId AND SCHEMA_NAME = CURRENT_SCHEMA;
		IF :exists1 <> 0 THEN
			CREATE LOCAL TEMPORARY COLUMN TABLE #REALSIZE_TMP("RealSizeOut" int);
			RealSize := 0;
			statement := 'INSERT INTO #REALSIZE_TMP SELECT MAX(LENGTH("U_' || :AliasID || '")) FROM "' || :TableId || '" WHERE "U_' || :AliasID || '" IS NOT NULL';
			EXEC :statement;
			SELECT TOP 1 "RealSizeOut" INTO RealSize FROM #REALSIZE_TMP;
			TRUNCATE TABLE #REALSIZE_TMP;

			SchemaSize := 0;
			statement := 'INSERT INTO #SCHEMASIZE_TMP SELECT "LENGTH" FROM TABLE_COLUMNS WHERE "TABLE_NAME" = ''' || :TableID || ''' AND "COLUMN_NAME" = ''U_' || :AliasID || ''' AND SCHEMA_NAME = CURRENT_SCHEMA';
			EXEC :statement;
			SELECT TOP 1 "SchemaSizeOut" INTO SchemaSize FROM #SCHEMASIZE_TMP;
			TRUNCATE TABLE #SCHEMASIZE_TMP;
			IF :RealSize <> 0 THEN
				IF :EditSize < :RealSize THEN
					INSERT INTO #UDF_TEMP VALUES(:TableID, 'U_' || :AliasId, :RealSize, :SchemaSize, :EditSize);
				ELSE 
					IF :SizeID < :RealSize THEN
						INSERT INTO #UDF_TEMP VALUES(:TableID, 'U_' || :AliasId, :RealSize, :SchemaSize, :EditSize);
					END IF;
				END IF;
			END IF;
			IF :SizeID > 254 OR :RealSize > 254 OR :SchemaSize > 254 THEN
				INSERT INTO #UDF_TEMP VALUES(:TableID, 'U_' || :AliasId, :RealSize, :SchemaSize, :SizeID);
			END IF;
			DROP TABLE #REALSIZE_TMP;
		END IF;
		FETCH alfa_udfs INTO TableID, AliasId, EditSize, SizeID;
	END WHILE;
	CLOSE alfa_udfs;
	SELECT * FROM #UDF_TEMP;
	DROP TABLE #UDF_TEMP;
	DROP TABLE #SCHEMASIZE_TMP;
END;







