Testing Table Metadata with QuerySurge

Versions: 8.0+

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