.::: Query to check table, column, compare schema database, structure table in Oracle Database :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. ALL_TABLES & ALL_TAB_COLUMNS

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'AISYAH';

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'TEGUHTH';
  

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'AISYAH';

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'TEGUHTH';
 

B. Query combine compare structure table

1. Menggunakan ALL_TAB_COLUMNS

SELECT
    'teguhth' AS DBNameA,
    a.TABLE_NAME AS table_name_DBNameA,
    a.COLUMN_NAME AS column_name_DBNameA,
    a.DATA_TYPE AS data_type_DBNameA,
    'aisyah' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB
FROM ALL_TAB_COLUMNS a
LEFT JOIN ALL_TAB_COLUMNS b
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND b.OWNER = 'AISYAH'
WHERE a.OWNER = 'TEGUHTH'

UNION

SELECT
    'teguhth' AS DBNameA,
    a.TABLE_NAME AS table_name_DBNameA,
    a.COLUMN_NAME AS column_name_DBNameA,
    a.DATA_TYPE AS data_type_DBNameA,
    'aisyah' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB
FROM ALL_TAB_COLUMNS b
LEFT JOIN ALL_TAB_COLUMNS a
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.OWNER = 'TEGUHTH'
WHERE b.OWNER = 'AISYAH'
ORDER BY table_name_DBNameA, column_name_DBNameA;
 

2. Menggunakan ALL_TAB_COLUMNS lebih detail

SELECT
    'teguhth' AS DBNameA,
    a.TABLE_NAME AS table_name_DBNameA,
    a.COLUMN_NAME AS column_name_DBNameA,
    a.DATA_TYPE AS data_type_DBNameA,
    a.DATA_LENGTH AS max_length_DBNameA,
    a.NULLABLE AS is_nullable_DBNameA,
    'aisyah' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB,
    b.DATA_LENGTH AS max_length_DBNameB,
    b.NULLABLE AS is_nullable_DBNameB
FROM ALL_TAB_COLUMNS a
LEFT JOIN ALL_TAB_COLUMNS b
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND b.OWNER = 'AISYAH'
WHERE a.OWNER = 'TEGUHTH'

UNION

SELECT
    'teguhth' AS DBNameA,
    a.TABLE_NAME AS table_name_DBNameA,
    a.COLUMN_NAME AS column_name_DBNameA,
    a.DATA_TYPE AS data_type_DBNameA,
    a.DATA_LENGTH AS max_length_DBNameA,
    a.NULLABLE AS is_nullable_DBNameA,
    'aisyah' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB,
    b.DATA_LENGTH AS max_length_DBNameB,
    b.NULLABLE AS is_nullable_DBNameB
FROM ALL_TAB_COLUMNS b
LEFT JOIN ALL_TAB_COLUMNS a
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.OWNER = 'TEGUHTH'
WHERE b.OWNER = 'AISYAH'
ORDER BY table_name_DBNameA, column_name_DBNameA;
 

3. Melihat Perbedaan Struktur dengan EXCEPT
Jika ingin melihat kolom yang hanya ada di salah satu database, gunakan:

Untuk mendapatkan kolom yang ada di teguhth tetapi tidak ada di aisyah:

Query ini akan menampilkan daftar kolom di teguhth yang tidak ada di aisyah. ??

SELECT a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE
FROM ALL_TAB_COLUMNS a
LEFT JOIN ALL_TAB_COLUMNS b
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.DATA_TYPE = b.DATA_TYPE
    AND b.OWNER = 'AISYAH'
WHERE a.OWNER = 'TEGUHTH'
AND b.COLUMN_NAME IS NULL;

Query ini akan menampilkan daftar kolom di aisyah yang tidak ada di teguhth. ??

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.DATA_TYPE
FROM ALL_TAB_COLUMNS b
LEFT JOIN ALL_TAB_COLUMNS a
    ON b.TABLE_NAME = a.TABLE_NAME
    AND b.COLUMN_NAME = a.COLUMN_NAME
    AND b.DATA_TYPE = a.DATA_TYPE
    AND a.OWNER = 'TEGUHTH'
WHERE b.OWNER = 'AISYAH'
AND a.COLUMN_NAME IS NULL;