.::: Query to check table, column, compare schema database, structure table in MariaDB MySQL :::.

 

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. INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'aisyahdb';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'aisyahdb';
 

 
SHOW TABLES FROM teguhth;
SHOW TABLES FROM aisyahdb;

SHOW COLUMNS FROM teguhth.barang;
SHOW COLUMNS FROM aisyahdb.barang;

 

B. Query combine compare structure table

1. Menggunakan INFORMATION_SCHEMA.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,
    'aisyahdb' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB
FROM INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND b.TABLE_SCHEMA = 'aisyahdb'
WHERE a.TABLE_SCHEMA = '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,
    'aisyahdb' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB
FROM INFORMATION_SCHEMA.COLUMNS b
LEFT JOIN INFORMATION_SCHEMA.COLUMNS a
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.TABLE_SCHEMA = 'teguhth'
WHERE b.TABLE_SCHEMA = 'aisyahdb'
ORDER BY table_name_DBNameA, column_name_DBNameA;
 

2. Menggunakan INFORMATION_SCHEMA.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.CHARACTER_MAXIMUM_LENGTH AS max_length_DBNameA,
    a.IS_NULLABLE AS is_nullable_DBNameA,
    'aisyahdb' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB,
    b.CHARACTER_MAXIMUM_LENGTH AS max_length_DBNameB,
    b.IS_NULLABLE AS is_nullable_DBNameB
FROM INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND b.TABLE_SCHEMA = 'aisyahdb'
WHERE a.TABLE_SCHEMA = '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.CHARACTER_MAXIMUM_LENGTH AS max_length_DBNameA,
    a.IS_NULLABLE AS is_nullable_DBNameA,
    'aisyahdb' AS DBNameB,
    b.TABLE_NAME AS table_name_DBNameB,
    b.COLUMN_NAME AS column_name_DBNameB,
    b.DATA_TYPE AS data_type_DBNameB,
    b.CHARACTER_MAXIMUM_LENGTH AS max_length_DBNameB,
    b.IS_NULLABLE AS is_nullable_DBNameB
FROM INFORMATION_SCHEMA.COLUMNS b
LEFT JOIN INFORMATION_SCHEMA.COLUMNS a
    ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COLUMN_NAME = b.COLUMN_NAME
    AND a.TABLE_SCHEMA = 'teguhth'
WHERE b.TABLE_SCHEMA = 'aisyahdb'
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 aisyahdb:

SELECT a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN INFORMATION_SCHEMA.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.TABLE_SCHEMA = 'aisyahdb'
WHERE a.TABLE_SCHEMA = 'teguhth'
AND b.COLUMN_NAME IS NULL;

dan sebaliknya
 

Jika ingin arah sebaliknya (yang ada di aisyahdb tapi tidak di teguhth)
 
SELECT b.TABLE_NAME, b.COLUMN_NAME, b.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS b
LEFT JOIN INFORMATION_SCHEMA.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.TABLE_SCHEMA = 'teguhth'
WHERE b.TABLE_SCHEMA = 'aisyahdb'
AND a.COLUMN_NAME IS NULL;