October 2009 - Posts

Navigation

October 2009 - Posts

Deleteting All Data in User Tables using Single SP

Menghapus Semua Data di Banyak Table mengunakan Sebuah Store Procedure

Pernah mencoba untuk menghapus semua record yang ada di semua table di sebuah database ?

tentunya bila menggunakan cara satu persatu table di DELETE atau di TRUNCATE akan sangat melelahkan jika jumlahnya banyak, apalagi bila databasenya sudah menjadi sangat rumit. Misalkan setiap table mempunyai ketergantungan/Depencies dengan banyak table lain,

Ada 2 hal dalam melakukan pekerjaan menhapus semua data dalam sebuah database :

Trigger, pastikan bahwa semua trigger yang telah terbuat didalam database tidak aktif
Consraints, Menonaktifkan data integrity pada coloumn yang telah kita tetapkan.

Berikut ini adalah Store Procedure untuk dapat melakukan pengahapusan seluruh data yang ada disemua tabel yang ada pada sebuah database :

CREATE PROCEDURE dbo.DeleteAllData
AS
BEGIN
DECLARE @SQL nvarchar(2000), @TableName sysname, @SchemaName sysname
DECLARE DaftarTable SCROLL CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name 
    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
  
OPEN DaftarTable
FETCH NEXT FROM DaftarTable INTO @SchemaName, @TableName
 
WHILE @@FETCH_STATUS = 0
BEGIN
--Disable Semua Trigger
    SET @SQL = 
    (SELECT    'ALTER TABLE '+ @SchemaName + '.' + @TableName + ' DISABLE TRIGGER ALL')
    EXECUTE sp_ExecuteSQL @SQL;    
    IF @@ERROR = 0
        BEGIN
            PRINT 'disabled Triggers ' + @SchemaName + '.' + @TableName +' Sukses !'
        END
    ELSE
        BEGIN
            PRINT 'Disabled Triggers ' + @SchemaName + '.' + @TableName +' Gagal :('
        END

    --Disable semua Constraint
    SET @SQL = 
    (SELECT    'ALTER TABLE '+ @SchemaName + '.' + @TableName + ' NOCHECK CONSTRAINT ALL')
    EXECUTE sp_ExecuteSQL @SQL;
    IF @@ERROR = 0
        BEGIN
            PRINT ' disabled Constraints ' + @SchemaName + '.' + @TableName +' Sukses !'
        END
    ELSE
        BEGIN
            PRINT ' disabled Constraints ' + @SchemaName + '.' + @TableName +' Gagal :('
        END
    FETCH NEXT FROM DaftarTable INTO @SchemaName, @TableName
END
    
FETCH FIRST FROM DaftarTable INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
         BEGIN
        SET @SQL = (SELECT    'BEGIN TRY 
        TRUNCATE TABLE ' + @SchemaName + '.' + @TableName + ' 
        PRINT ''Berhasil melakukan pekerjaan Truncated pada ' + @SchemaName + '.' + @TableName + ''' 
        END TRY 
        BEGIN CATCH 
        DELETE FROM ' + @SchemaName + '.' + @TableName + '
        IF EXISTS(SELECT ''A'' FROM information_schema.columns 
            WHERE COLUMNPROPERTY(OBJECT_ID(''' + @SchemaName + '.' + @TableName + '''), 
            column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @SchemaName + '''
            AND QUOTENAME(TABLE_NAME) = ''' + @TableName + ''')
            BEGIN
                DBCC CHECKIDENT(''' + @SchemaName + '.' + @TableName + ''', RESEED, 0) 
            END
        PRINT ''Berhasil melakukan Penghapusan data pada ' + @SchemaName + '.' + @TableName + ''' 
        END CATCH')
        END
        
    EXECUTE sp_ExecuteSQL @SQL;
    
    FETCH NEXT FROM DaftarTable INTO @SchemaName, @TableName
END
 
FETCH FIRST FROM DaftarTable INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--Enable Semua Trigger
    SET @SQL = 
    (SELECT    'ALTER TABLE '+ @SchemaName + '.' + @TableName + ' ENABLE TRIGGER ALL')
    EXECUTE sp_ExecuteSQL @SQL;    
    IF @@ERROR = 0
        BEGIN
            PRINT 'Enabled Triggers ' + @SchemaName + '.' + @TableName +' Sukses !'
        END
    ELSE
        BEGIN
            PRINT 'Enabled Triggers ' + @SchemaName + '.' + @TableName +' Gagal :('
        END
        
    SET @SQL = (SELECT    'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' CHECK CONSTRAINT ALL')
    EXECUTE sp_ExecuteSQL @SQL;
 
    IF @@ERROR = 0
        BEGIN
            PRINT ' Enabled Constraints ' + @SchemaName + '.' + @TableName +' Sukses !'
        END
    ELSE
        BEGIN
            PRINT ' Enabled Constraints ' + @SchemaName + '.' + @TableName +' Gagal :('
        END
    FETCH NEXT FROM DaftarTable INTO @SchemaName, @TableName
END
 
CLOSE DaftarTable
DEALLOCATE DaftarTable
 
END