Articles in this section

See more

Testing Table Metadata with QuerySurge

One of the common challenges that QuerySurge users face is testing with Metadata. Typically, this need arises in data migration projects, where databases are either migrating in version (i.e. a database upgrade) or migrating in technology (i.e. a migration from one database technology to another).

Metadata tests are performed in QuerySurge much like every other data test - by querying. In this case, the QueryPairs that are written are against database "information schema", that is, the schema where the database keeps the table metadata for application or user schema. In this article we provide a basic illustration showing how metadata is tested between a SQL Server 2012 database and an Oracle 10g database.

Example 1: Verification of Tables, Columns and Column Order

In Example 1, we show a simple test of core table layout: table names, column names and column order. 

SQL Server 2012 Oracle 10g
SELECT
    TABLE_CATALOG
  , TABLE_NAME
  , COLUMN_NAME
  , ORDINAL_POSITION
FROM information_schema.columns
WHERE TABLE_CATALOG = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, ORDINAL_POSITION

SELECT
    OWNER
  , TABLE_NAME
  , COLUMN_NAME
  , COLUMN_ID
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, COLUMN_ID

 

Example 2: Verification of Columns and Data Types

In Example 2, we have elaborated the test of core table layout to include column data types in addition to the properties shown in Example 1. Note that we order by appropriate columns to make the results as visually meaningful as we can.

SQL Server 2012 Oracle 10g
SELECT
  TABLE_CATALOG
, TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, DATA_TYPE
FROM information_schema.columns
WHERE TABLE_CATALOG = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, ORDINAL_POSITION

SELECT
  OWNER
, TABLE_NAME
, COLUMN_NAME
, COLUMN_ID
, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, COLUMN_ID

 

Example 3: More on Verification of Data Types

In Example 3, we look at the problem of different data type names in different database technologies. Correlating data types in different technologies may require custom work, since an organization may decide to use a related but not identical data type in the migration target. Note that the type correlations are all performed in the SQL Server query in this example; in this query, we have: SQL Server nchar type correlated to Oracle CHAR type, SQL Server nvarchar type correlated to Oracle VARCHAR2 type, etc.

SQL Server 2012 Oracle 10g
SELECT
    TABLE_CATALOG
  , TABLE_NAME
  , COLUMN_NAME
  , ORDINAL_POSITION
  , CASE
      WHEN (DATA_TYPE = 'nchar')
        THEN 'CHAR'
      WHEN (DATA_TYPE = 'nvarchar')
        THEN 'VARCHAR2'
      WHEN (DATA_TYPE = 'datetime2')
        THEN 'DATE'
      WHEN (DATA_TYPE = 'varchar')
        THEN 'VARCHAR2'
      WHEN (DATA_TYPE = 'decimal')
        THEN 'NUMBER'
      WHEN (DATA_TYPE = 'float')
        THEN 'FLOAT'
    ELSE DATA_TYPE END DATA_TYPE
FROM information_schema.columns
WHERE TABLE_CATALOG = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, ORDINAL_POSITION

SELECT
    OWNER
  , TABLE_NAME
  , COLUMN_NAME
  , COLUMN_ID
  , DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SALES'
AND TABLE_NAME IN ('CUSTOMER','CUSTOMERADDRESS', 'ORDERS', 'ORDERSTATUS', 'PAYMENT', 'PAYMENTMONTH', 'PAYMENTTYPE', 'SHIPPER', 'SHIPPINGRATE', 'SUPPLIER')
ORDER BY TABLE_NAME, COLUMN_ID

 

 

 

 

 

 

 

 

 

Related articles