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