.::: T-SQL Script to Drop All Tables: Foreign-Key Tables First, Then Other Tables, While Excluding One Table (e.g., barang) :::.

 

Berikut beberapa pilihan query SQL Server untuk menampilkan daftar tabel yang memiliki Foreign Key dan diurutkan.

1. Urut berdasarkan nama tabel
SELECT DISTINCT 
    t.name AS TableName
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables t ON fk.parent_object_id = t.object_id
ORDER BY 
    t.name;
 

2. Detail lebih lengkap (FK name, referenced table)
SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tr.name AS ReferencedTable
FROM 
    sys.foreign_keys fk
JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
ORDER BY 
    tp.name, fk.name;
 

3. Tampilkan juga kolom yang terkait FK
SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN 
    sys.tables tp ON fkc.parent_object_id = tp.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_object_id = cp.object_id 
                   AND fkc.parent_column_id = cp.column_id
INNER JOIN 
    sys.tables tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_object_id = cr.object_id 
                   AND fkc.referenced_column_id = cr.column_id
ORDER BY 
    tp.name, fk.name, cp.name;
 

4. Hitung jumlah FK per tabel
SELECT 
    t.name AS TableName,
    COUNT(fk.object_id) AS TotalForeignKey
FROM 
    sys.tables t
LEFT JOIN 
    sys.foreign_keys fk ON fk.parent_object_id = t.object_id
GROUP BY 
    t.name
HAVING 
    COUNT(fk.object_id) > 0
ORDER BY 
    TotalForeignKey DESC;
 

5. list table sql server dan urutkan berdasakan foreigkey dahulu baru kemudian table yang lain dalam sebuah database misalkan teguhth

USE teguhth;
GO

SELECT 
    t.name AS TableName,
    ISNULL(fk.CountFK, 0) AS TotalForeignKey
FROM 
    sys.tables t
LEFT JOIN 
    (
        SELECT 
            parent_object_id, 
            COUNT(*) AS CountFK
        FROM 
            sys.foreign_keys
        GROUP BY 
            parent_object_id
    ) fk ON t.object_id = fk.parent_object_id
ORDER BY
    CASE WHEN fk.CountFK > 0 THEN 0 ELSE 1 END,
    fk.CountFK DESC,
    t.name ASC;
 

6. untuk no 5 tapi table barang dikecualikan

SELECT 
    t.name AS TableName,
    ISNULL(fk.CountFK, 0) AS TotalForeignKey
FROM 
    sys.tables t
LEFT JOIN 
    (
        SELECT 
            parent_object_id, 
            COUNT(*) AS CountFK
        FROM 
            sys.foreign_keys
        GROUP BY 
            parent_object_id
    ) fk ON t.object_id = fk.parent_object_id
WHERE 
    t.name <> 'barang'   -- Mengecualikan tabel barang
ORDER BY
    CASE WHEN fk.CountFK > 0 THEN 0 ELSE 1 END,
    fk.CountFK DESC,
    t.name ASC;
 

7. list script drop table 

SELECT 
    'DROP TABLE [' + t.name + '];' AS DropCommand,
    t.name AS TableName,
    ISNULL(fk.CountFK, 0) AS TotalForeignKey
FROM 
    sys.tables t
LEFT JOIN (
        SELECT parent_object_id, COUNT(*) AS CountFK
        FROM sys.foreign_keys
        GROUP BY parent_object_id
) fk ON t.object_id = fk.parent_object_id
WHERE 
    t.name <> 'barang'
ORDER BY 
    CASE WHEN fk.CountFK > 0 THEN 0 ELSE 1 END,
    fk.CountFK DESC,
    t.name ASC;
 

7. Jalankan run drop table kecuali table barang

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH TableFK AS (
    SELECT 
        t.name AS TableName,
        COUNT(fk.object_id) AS FKCount
    FROM 
        sys.tables t
    LEFT JOIN 
        sys.foreign_keys fk ON t.object_id = fk.parent_object_id
    WHERE 
        t.name <> 'barang'
    GROUP BY 
        t.name
)
SELECT @sql = STRING_AGG(
        'DROP TABLE [' + TableName + '];', CHAR(10)
    )
    WITHIN GROUP (ORDER BY 
        CASE WHEN FKCount > 0 THEN 0 ELSE 1 END,  -- FK duluan
        FKCount DESC,
        TableName ASC
    )
FROM TableFK;

PRINT @sql;   -- Review dulu hasilnya!
-- EXEC(@sql); -- Setelah yakin, jalankan ini