.::: Query to check table, column, compare schema database, structure table in EDB PostgreSQL :::.

 

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

## from teguhth akses teguhth
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'teguhth' AND TABLE_SCHEMA = 'public';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'public';

## from teguhth akses aisyahdb
SELECT * FROM dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_catalog, table_schema, table_name, table_type
     FROM information_schema.tables WHERE table_schema = ''public''')
AS t(table_catalog text, table_schema text, table_name text, table_type text);
 
SELECT * FROM dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_catalog, table_schema, table_name, column_name, data_type
     FROM information_schema.columns WHERE table_schema = ''public''')
AS t(table_catalog text, table_schema text, table_name text, column_name text, data_type text);
 

 

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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text)
ON a.table_name = b.table_name AND a.column_name = b.column_name
WHERE a.table_schema = 'public'

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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text)
LEFT JOIN information_schema.columns a
ON a.table_name = b.table_name AND a.column_name = b.column_name
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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type, character_maximum_length, is_nullable
     FROM information_schema.columns
     WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text, character_maximum_length int, is_nullable text)
ON a.table_name = b.table_name AND a.column_name = b.column_name
WHERE a.table_schema = 'public'

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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type, character_maximum_length, is_nullable
     FROM information_schema.columns
     WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text, character_maximum_length int, is_nullable text)
LEFT JOIN information_schema.columns a
ON a.table_name = b.table_name AND a.column_name = b.column_name
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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type
     FROM information_schema.columns
     WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text)
ON a.table_name = b.table_name
AND a.column_name = b.column_name
AND a.data_type = b.data_type
WHERE a.table_schema = 'public'
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 dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_name, column_name, data_type
     FROM information_schema.columns
     WHERE table_schema = ''public''')
    AS b(table_name text, column_name text, data_type text)
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
WHERE a.table_name IS NULL;