Menghapus semua data dalam table pada sebuah database
Pernahkah anda merasa
seperti wasting time untuk
mengosongkan data sebuah database?Apalagi pada database
tersebut banyak table yang di-reference
dan ter-reference dengan table lainnya,
tentunya anda harus menelusuri terlebih dahulu semua constraint yang ada dalam database tersebut, karena sebuah parent
table tidak akan bisa dihapus datanya apabila data dalam parent table
tersebut sudah di reference oleh child table-nya, dan constraint yang ada tidak
memperbolehkan penghapusan secara cascade.
Dari kasus diatas
bagaimana cara tercepat untuk melakukan penghapusan data pada sebuah database?
Kita lakukan dengan
sedikit logic/algoritma sederhana dalam melakukan penghapusan ini.
Pertama, kita lakukan query
terhadap system objects untuk me-list down semua user table yang ada pada
database yang akan kita kosongkan datanya. Dalam Microsoft SQL Server 2005 ada
system view yang fungsinya untuk menampilkan semua user table yang ada dalam
sebuah database, kita gunakan view INFORMATION_SCHEMA.TABLES.
SELECT Table_Name FROM
INFORMATION_SCHEMA.TABLES WHERE Table_Type = ‘BASE TABLE'
Query tersebut diatas akan
menampilkan semua user table pada database aktif. Apabila kita jalankan query
tersebut pada database AdventureWorks, maka SQL akan menampilkan list of user
table yang ada pada database AdventureWorks.
Kedua, setelah kita
dapatkan list of table tersebut, kita lakukan query untuk men-disable-kan
constraint yang ada pada tiap-tiap table tadi. Tentunya supaya bisa terjadi
proses looping/pengulangan untuk tiap-tiap table yang ada pada sebuah database
tadi, maka kita harus tampung list of table tadi dalam sebuah cursor.
DECLARE cTable CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type = ‘BASE
TABLE'
Script tersebut akan
membuat sebuah cursor yang isinya adalah list of table dalam sebuah database
aktif tadi.
Ketiga, kita harus lakukan
sebuah loop dari cursor tadi untuk men-generate sebuat script query yang
fungsinya untuk men-disable-kan constraint-constraint yang ada pada list of
table tadi, kemudian script hasil generated tadi kita execute.
DECLARE @Query
NVARCHAR(4000)
DECLARE @TableName SYSNAME
DECLARE cTable CURSOR FOR
SELECT Table_Schema + ‘.' + Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE
Table_Type = 'BASE TABLE'
OPEN cTable
FETCH NEXT FROM cTable
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = ‘ALTER TABLE ‘+@TableName+' NOCHECK CONSTRAINT
ALL'
EXEC sp_executesql @Query
FETCH NEXT FROM cTable INTO @TableName
END
SET @Query = ‘ALTER TABLE
‘+@TableName+' NOCHECK CONSTRAINT ALL'
EXEC sp_executesql @Query
CLOSE cTable
Script diatas akan
men-disable semua constraint yang ada pada tiap-tiap table yang akan kita
kosongkan datanya.
Keempat, setelah kita
men-disable-kan semua constraint, kita tinggal generate script untuk melakukan
proses delete pada tiap-tiap table tadi dengan melakukan looping/pengulangan
pada cursor yang tadi sudah kita buat.
OPEN cTable
FETCH NEXT FROM cTable
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = ‘DELETE ‘+@TableName
EXEC sp_executesql @Query
FETCH NEXT FROM cTable INTO @TableName
END
SET @Query = ‘DELETE
‘+@TableName
EXEC sp_executesql @Query
CLOSE cTable
Script diatas akan
menghapus semua user table tadi.
Kelima, setelah kita
disable-kan constraint dan menghapus data yang ada pada tiap-tiap table,
tentunya kita harus me-enable-kan kembali constraint yang ada pada tiap-tiap
table tadi.
OPEN cTable
FETCH NEXT FROM cTable
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = ‘ALTER TABLE ‘+@TableName + ‘ WITH CHECK CHECK
CONSTRAINT ALL'
EXEC sp_executesql @Query
FETCH NEXT FROM cTable INTO @TableName
END
SET @Query = ‘ALTER TABLE
‘+@TableName + ‘ WITH CHECK CHECK CONSTRAINT ALL'
EXEC sp_executesql @Query
CLOSE cTable
DEALLOCATE cTable
Script diatas akan kembali
me-enable-kan semua constraint yang ada pada tiap-tiap table.
Step by step diatas adalah
step by step detailnya, sebetulnya proses looping/pengulangan bisa dilakukan
hanya sekali saja, dimana proses generate scriptnya langsung ada 3, yaitu,
untuk men-disable-kan constraint, men-delete data dan me-enable-kan kembali
constraint.
Script lengkap dari
artikel diatas (stored procedure) bisa didownload disini.
Kesimpulan, dengan sedikit
bantuan system objects yang ada dalam database, kita bisa melakukan suatu trik
tertentu yang bisa diaplikasikan pada database kita.