.::: Query to check table, column, compare schema database, structure table in SQL Server :::.

 

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 teguhth.INFORMATION_SCHEMA.tables
select * from aisyah.INFORMATION_SCHEMA.tables

select * from teguhth.INFORMATION_SCHEMA.COLUMNS
select * from aisyah.INFORMATION_SCHEMA.COLUMNS
 

 

2. sys.tables sys.columns

select * from teguhth.sys.tables
select * from aisyah.sys.tables

select * from teguhth.sys.columns
select * from aisyah.sys.columns
 

B. Query combine compare structure table

1. Menggunakan INFORMATION_SCHEMA.COLUMNS

SELECT @@servername as ServerName,
    '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 teguhth.INFORMATION_SCHEMA.COLUMNS a
FULL OUTER JOIN aisyah.INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
ORDER BY a.TABLE_NAME, a.COLUMN_NAME;
 
Penjelasan:

Menggunakan INFORMATION_SCHEMA.COLUMNS untuk mendapatkan informasi tabel dan kolom dari masing-masing database.
FULL OUTER JOIN digunakan agar menampilkan perbedaan (jika ada tabel atau kolom yang hanya ada di salah satu database).
Menyusun hasil berdasarkan nama tabel dan kolom.
 

2. Menggunakan sys.columns dan sys.tables untuk Detail Lebih Lengkap
Jika ingin membandingkan ukuran kolom, tipe data, dan atribut lain, gunakan query ini:

SELECT @@servername as Servername,
    'teguhth' as DBNameA,
    t1.name AS table_name_DBNameA,
    c1.name AS column_name_DBNameA,
    ty1.name AS data_type_DBNameA,
    c1.max_length AS max_length_DBNameA,
    c1.is_nullable AS is_nullable_DBNameA,
    'aisyah' as DBNameB,
    t2.name AS table_name_DBNameB,
    c2.name AS column_name_DBNameB,
    ty2.name AS data_type_DBNameB,
    c2.max_length AS max_length_DBNameB,
    c2.is_nullable AS is_nullable_DBNameB

FROM teguhth.sys.tables t1
FULL OUTER JOIN aisyah.sys.tables t2
ON t1.name = t2.name

FULL OUTER JOIN teguhth.sys.columns c1
ON t1.object_id = c1.object_id

FULL OUTER JOIN aisyah.sys.columns c2
ON t2.object_id = c2.object_id
AND c1.name = c2.name

LEFT JOIN teguhth.sys.types ty1
ON c1.user_type_id = ty1.user_type_id

LEFT JOIN aisyah.sys.types ty2
ON c2.user_type_id = ty2.user_type_id

WHERE t1.name IS NOT NULL OR t2.name IS NOT NULL  -- Tambahkan filter agar salah satu tabel harus ada
ORDER BY t1.name, c1.name;

Kelebihan Query Ini: ? Menampilkan panjang kolom (max_length) dan apakah kolom dapat NULL.
? Bisa mendeteksi tabel atau kolom yang hanya ada di salah satu database.
? Lebih detail dibanding INFORMATION_SCHEMA.COLUMNS.
 

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

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM teguhth.INFORMATION_SCHEMA.COLUMNS
EXCEPT
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM aisyah.INFORMATION_SCHEMA.COLUMNS;
 
Dan sebaliknya:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM aisyah.INFORMATION_SCHEMA.COLUMNS
EXCEPT
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM teguhth.INFORMATION_SCHEMA.COLUMNS;
 

4. Compare table

SELECT @@servername as Servername,
    'teguhth' as DBNameA,
    t1.name AS table_name_DBNameA,
    'aisyah' as DBNameB,
    t2.name AS table_name_DBNameB
FROM teguhth.sys.tables t1
FULL OUTER JOIN aisyah.sys.tables t2
ON t1.name = t2.name
WHERE t1.name IS NOT NULL OR t2.name IS NOT NULL  -- Pastikan minimal ada di salah satu database
ORDER BY COALESCE(t1.name, t2.name);

5. compare view

SELECT
    @@servername AS Servername,
    'teguhth' AS DBNameA,
    t1.name AS object_name_DBNameA,
    t1.type_desc AS object_type_DBNameA,
    'aisyah' AS DBNameB,
    t2.name AS object_name_DBNameB,
    t2.type_desc AS object_type_DBNameB
FROM teguhth.sys.objects t1
FULL OUTER JOIN aisyah.sys.objects t2
ON t1.name = t2.name AND t1.type = t2.type
WHERE (t1.type IN ('V', 'P', 'TR', 'FN', 'IF', 'TF')
    OR t2.type IN ('V', 'P', 'TR', 'FN', 'IF', 'TF'))
ORDER BY COALESCE(t1.name, t2.name);

Kesimpulan
Query pertama cocok untuk perbandingan umum struktur tabel.
Query kedua lebih detail (termasuk panjang kolom dan nullable).
Query ketiga menampilkan perbedaan unik antara dua database.
Silakan dicoba! Jika butuh hasil yang lebih spesifik, bisa saya bantu modifikasi query-nya.