Recursive delete procedure


The following procedure can be used in development systems to remove data from tables which are referenced by other tables. The procedure recursively follows all the references and deletes all referening tables first.

Be careful, when you use it! All data of all referenced tables will be deleted.

create procedure temp.RecursiveDelete
(
     @tablename nvarchar(1000
)
     ,@schemaname nvarchar(1000)
)
as

declare @oid as int
declare @parentSchemaName as varchar(max
)
declare @parentObjectName as varchar(max
)

select @oid = t.object_id from sys.tables t join sys.schemas s on t.schema_id = s.schema_id where t.name = @tablename
and s.name =
@schemaname

declare @parentOid int

declare
fkCursor cursor LOCAL for select parent_object_id, OBJECT_NAME(parent_object_id) from sys.foreign_keys where referenced_object_id = @oid

open fkCursor

fetch next from fkCursor into @parentOid, @parentObjectName

while @@FETCH_STATUS = 0
Begin
   select @parentSchemaName = s.name from sys.schemas s join sys.tables
t
      on s.schema_id = t.
schema_id
   where t.object_id
=@parentOid

   exec temp.RecursiveDelete @parentObjectName,
   @parentSchemaName

   fetch
next from fkCursor into @parentOid, @parentObjectName
End

-- delete data
declare @delStmt nvarchar(max
)

set @delStmt = 'delete from [' + @schemaname + '].[' + @tablename + ']'

exec sp_executesql @delStmt

close
fkCursor
deallocate
fkCursor

go


Comments (1)
  1. AYZIT says:

    Hi,

    That is a good article. Is there C# version?

    Regards

Comments are closed.

Skip to main content