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