ClearData

,

We can use this sp to clear data in tables.  I mainly created this sp for two reason. 1.This is useful to commence the testing from scratch.  2.Whenever we ask the database backup from our customer.  They hesitant to give the production database because of confidential.  I have used Delete and Truncate statement to clear the data.  Please refer SQL Books online difference of Delete and Truncate statement.

/* To Execute the sp 
--exec ClearTableData 
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[ClearTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClearTableData]
GO

Create Proc ClearTableData 
As

Declare @TableName Varchar(1000), @Reference Varchar(4), @SQL Varchar(8000), @ObjectName Varchar(8000), 
        @TabName Varchar(1000)

SET NOCOUNT ON

If db_name() In ('master', 'model', 'msdb', 'tempdb')
    Begin
        Print 'system database are not allowed to clear'
        return 0
    End

Set @ObjectName = ''
Set @TabName = ''

-- Delete data in Child Object and its Parent Object 
Begin
    DECLARE ClearTableData_Cursor CURSOR FOR 
    Select SO.Name "ChildObject", 
            Case When (Select Count(*) From SysReferences Where rkeyid = SO.id) = 0 Then 'N' Else 'Y' End As Ref
    From SysObjects SO, SysReferences SR, SysObjects SO1
    Where SO.id = SR.fkeyid And SO.xtype = 'U' And SO1.ID = SR.rkeyid 
    Order By SO1.Name Desc, SO.Name 
    OPEN ClearTableData_Cursor
  
    FETCH NEXT FROM ClearTableData_Cursor 
    INTO @TableName, @Reference
  
    WHILE @@FETCH_STATUS = 0
    BEGIN
        If @Reference = 'Y'
        Begin
--    Concatenate Primary Key Table name
            Set @ObjectName = @ObjectName + ',' + @TableName
        End
        Else If @Reference = 'N'
        Begin
            Begin
--    Clear data from Foreign Key Tables(Child object)
                Set @SQL = ''
                Set @SQL = 'Truncate Table [' + @TableName + ']'
                exec (@SQL)
            End
        End               
    
        FETCH NEXT FROM ClearTableData_Cursor 
        INTO @TableName, @Reference
    END
  
    CLOSE ClearTableData_Cursor
    DEALLOCATE ClearTableData_Cursor

    If Len(@Reference) > 0
        Begin 
--    Clear data for Concatenated Primary Key Tables
            Set @ObjectName = Right(@ObjectName,Len(@ObjectName)-1)

            While Len(@ObjectName) > 0
                Begin
                    If (CHARINDEX(',',@ObjectName) > 0)
                        Begin
                            Set @TabName = Substring(@ObjectName, 1, CharIndex(',',@ObjectName)-1)
                            Set @ObjectName = SUBSTRING(@ObjectName, Len(@TabName) + 2, Len(@ObjectName))
                        End    
                    Else
                        Begin
                            Set @TabName = @ObjectName
                            Set @ObjectName = ''
                        End
                        Begin    
                            Set @SQL = ''
                            Set @SQL = 'DELETE From [' + @TabName + ']'
                            exec (@SQL)
                        End
                End
        End  
End
--Delete Data in Standalone Parent Object
Begin
    DECLARE ClearTableData_Cursor CURSOR FOR 
    Select Distinct SO.Name "ChildObject"
    From SysObjects SO
    Where (SO.id In (Select rkeyid From SysReferences))
    And SO.xtype = 'U'  
    Order By SO.Name 
    OPEN ClearTableData_Cursor
  
    FETCH NEXT FROM ClearTableData_Cursor 
    INTO @TableName
  
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Begin
            Set @SQL = ''
            Set @SQL = 'DELETE From [' + @TableName + ']'
            exec (@SQL)
        End              
    
        FETCH NEXT FROM ClearTableData_Cursor 
        INTO @TableName
    END
  
    CLOSE ClearTableData_Cursor
    DEALLOCATE ClearTableData_Cursor
End
-- Delete data for BaseDate/Master Table
Begin
    DECLARE ClearTableData_Cursor CURSOR FOR 
    Select Distinct SO.Name "ChildObject"
    From SysObjects SO
    Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
    And SO.xtype = 'U' 
    Order By SO.Name 

    OPEN ClearTableData_Cursor
  
    FETCH NEXT FROM ClearTableData_Cursor 
    INTO @TableName
  
    WHILE @@FETCH_STATUS = 0
    BEGIN

        Begin
            Set @SQL = ''
            Set @SQL = 'Truncate Table [' + @TableName + ']'
            exec (@SQL)
        End               
    
        FETCH NEXT FROM ClearTableData_Cursor 
        INTO @TableName
    END
  
    CLOSE ClearTableData_Cursor
    DEALLOCATE ClearTableData_Cursor
End
SET NOCOUNT OFF

Rate

Share

Share

Rate