CREATE PROCEDURE [dbo].[VINDEX]  
   @input_table_name nvarchar(max),
   @input_field_name nvarchar(max),
   @input_index_name nvarchar(max),
   @input_unique 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.
         */

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

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

         @field_count float(53)
    
      SELECT @index_count = count(0)
      FROM sys.indexes AS ind
      WHERE ind.name = @input_index_name AND
            OBJECT_NAME(ind.object_id) = @input_table_name      

      IF @index_count = 0
         PRINT 'ERROR:  Verify index ' + ISNULL(@input_index_name, '') + ' for table ' + ISNULL(@input_table_name, '') + ' failed.  Index not found.'

      IF @index_count = 1
         BEGIN

            /* assume uniqueness is not found and check*/
            SELECT @unique_flag = 0

            SELECT @unique_value = is_unique
            FROM sys.indexes AS ind
            WHERE ind.name = @input_index_name AND
                  OBJECT_NAME(ind.object_id) = @input_table_name
           
            IF @input_unique = @unique_value
               SELECT @unique_flag = 1

            /* print unique attribute results*/
            IF @unique_flag = 0
               PRINT
                  'ERROR:  Verify unique attribute of index '
                   +
                  ISNULL(@input_index_name, '')
                   +
                  ' on table '
                   +
                  ISNULL(@input_table_name, '')
                   +
                  ' failed.  Unique attribute input was '
                   +
                  ISNULL(@input_unique, '')
                   +
                  ' and found was '
                   +
                  ISNULL(@unique_value, '')
                   +
                  '.'

               SELECT @field_count = count(0)
            FROM sys.index_columns AS indcol
            WHERE OBJECT_NAME(indcol.object_id)=@input_table_name AND
                  COL_NAME(indcol.object_id,indcol.column_id)=@input_field_name

            /* print index field results*/
            IF @field_count = 0
               PRINT
                  'ERROR:  Verify index '
                   +
                  ISNULL(@input_index_name, '')
                   +
                  ' on field '
                   +
                  ISNULL(@input_table_name, '')
                   +
                  '.'
                   +
                  ISNULL(@input_field_name, '')
                   +
                  ' failed.  Field not found on index.'

            /* print success if all tests pass*/
            IF
               @unique_flag = 1 AND
               @field_count = 1 AND
               @input_full_report = 1
               PRINT 'Verify index ' + ISNULL(@input_index_name, '') + ' on table ' + ISNULL(@input_table_name, '') + ' succeeded.'

         END

   END
GO