CREATE PROCEDURE [dbo].[VFIELD]  
   @input_table_name nvarchar(max),
   @input_field_name nvarchar(max),
   @input_data_type nvarchar(max),
   /*
   *   SSMA warning messages:
   *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
   */

   @input_data_size float(53) = -1,
   @input_isnullable nvarchar(max),
   /*
   *   SSMA warning messages:
   *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
   */

   @input_full_report float(53) = 0
AS
   
   /*
   *   Generated by SQL Server Migration Assistant for Oracle.
   *   Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
   BEGIN

      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @field_count float(53),
         @data_type nvarchar(100),
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @data_size float(53),
         @isnullable nvarchar(2),
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @data_type_flag float(53),
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @data_size_flag float(53),
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @isnullable_flag float(53)

      SELECT @field_count = count(0)
      FROM sys.all_columns
      WHERE OBJECT_NAME(object_id)=@input_table_name and NAME=@input_field_name

      IF @field_count = 0
         PRINT 'ERROR:  Verify field ' + ISNULL(@input_table_name, '') + '.' + ISNULL(@input_field_name, '') + ' failed. Field not found in table.'

      IF @field_count > 0
         BEGIN

              SELECT @data_type = st.name, @data_size = alc.max_length, @isnullable = alc.is_nullable
            FROM sys.all_columns AS alc
            INNER JOIN sys.types AS st ON
            alc.user_type_id = st.user_type_id         
            WHERE OBJECT_NAME(alc.object_id)=@input_table_name and alc.name=@input_field_name    

            /* assume data type is not correct and check*/
            SELECT @data_type_flag = 0

            IF @input_data_type = @data_type
               SELECT @data_type_flag = 1

            /* print data type results*/
            IF @data_type_flag = 1 AND @input_full_report = 1
               PRINT 'Verify data type for field ' + ISNULL(@input_table_name, '') + '.' + ISNULL(@input_field_name, '') + ' succeeded.'

            IF @data_type_flag = 0
               PRINT
                  'ERROR:  Verify data type for field '
                   +
                  ISNULL(@input_table_name, '')
                   +
                  '.'
                   +
                  ISNULL(@input_field_name, '')
                   +
                  ' failed.  Data type input was '
                   +
                  ISNULL(@input_data_type, '')
                   +
                  ' and data type found was '
                   +
                  ISNULL(@data_type, '')
                   +
                  '.'

            /* assume data size is not correct*/
            SELECT @data_size_flag = 0

            /* check data size for text and binary fields*/
            IF @input_data_type IN (  'NVARCHAR2' ) AND @input_data_size = @data_size / 2
               SELECT @data_size_flag = 1

            IF @input_data_type IN (  'RAW' ) AND @input_data_size = @data_size
               SELECT @data_size_flag = 1

            IF @input_data_type NOT IN ( 'NVARCHAR2', 'RAW' )
               SELECT @data_size_flag = 1

            /* print data size results*/
            IF @data_size_flag = 1 AND @input_full_report = 1
               PRINT 'Verify data size for field ' + ISNULL(@input_table_name, '') + '.' + ISNULL(@input_field_name, '') + ' succeeded.'

            IF @data_size_flag = 0
               PRINT
                  'ERROR:  Verify data size for field '
                   +
                  ISNULL(@input_table_name, '')
                   +
                  '.'
                   +
                  ISNULL(@input_field_name, '')
                   +
                  ' failed.  Data size input was '
                   +
                  ISNULL(CAST(@input_data_size AS varchar(max)), '')
                   +
                  ' and data size found was '
                   +
                  ISNULL(CAST(@data_size / 2 AS varchar(max)), '')
                   +
                  '.'

            /* assume isnullable is not correct and check value*/
            SELECT @isnullable_flag = 0

            IF @input_isnullable = @isnullable
               SELECT @isnullable_flag = 1

            /* print isnullable results*/
            IF @isnullable_flag = 1 AND @input_full_report = 1
               PRINT 'Verify nullable for field ' + ISNULL(@input_table_name, '') + '.' + ISNULL(@input_field_name, '') + ' succeeded.'

            IF @isnullable_flag = 0
               PRINT
                  'ERROR:  Verify nullable for field '
                   +
                  ISNULL(@input_table_name, '')
                   +
                  '.'
                   +
                  ISNULL(@input_field_name, '')
                   +
                  ' failed.  Nullable input was '
                   +
                  ISNULL(@input_isnullable, '')
                   +
                  ' and nullable found was '
                   +
                  ISNULL(@isnullable, '')
                   +
                  '.'

            /* print results for entire field check*/
            IF
               @data_type_flag = 1 AND
               @data_size_flag = 1 AND
               @isnullable_flag = 1 AND
               @input_full_report = 1
               PRINT 'Verify field ' + ISNULL(@input_table_name, '') + '.' + ISNULL(@input_field_name, '') + ' succeeded.'

         END

   END
GO