ClearData

/* 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