Clear database - delete all records from a database (updated)

/*****************************************************************************************************************************************
*
* Author Rafal Skotak
* Purpose This procedure deletes all records from all tables in the specified database 
* (except schema 'sys' and databases master, model, tempdb and msdb)
* At first all constraints are disabled, afterwards all records are deleted 
* (truncate would not work due to foreign keys) and 
* finally all constraints are enabled
* Date 2008.01.31
*      2008.04.16 Updated
*
******************************************************************************************************************************************/

if exists(select * from sys.objects where object_id = object_id('dbo.proc_clear_database') and type = 'P')
drop procedure dbo.proc_clear_database;
go

create procedure dbo.proc_clear_database
@db_name sysname = null,
@reset_ident tinyint = 1
as
begin
set nocount on

if @db_name is null
set @db_name = db_name()

if @reset_ident is null
set @reset_ident = 0

if not exists(select * from master.sys.databases where name = @db_name and database_id > 4)
begin
raiserror('Database does not exist or it can not be cleared', 16, 1)
return
end

declare @recovery_mode sysname

select @recovery_mode = cast(DatabasePropertyEx(@db_name,'Recovery') as sysname)

if @recovery_mode <> 'Simple'
begin
declare @ncmd nvarchar(max)

print '-- Changing database recovery mode to SIMPLE'

set @ncmd = 'alter database [' + @db_name + '] set recovery simple'

print @ncmd

exec sp_executesql @ncmd
end

-------------------------------------------------------------------------------------------
-- prepare table with tables list

create table #temp_tables
(
rec_id int identity(1, 1) primary key not null,
schema_name sysname not null,
table_name sysname not null
)

declare @n_cmd nvarchar(max)

set @n_cmd = 'insert into #temp_tables (schema_name, table_name) select ss.name as schema_name, st.name as table_name from [' + @db_name + '].sys.tables as st inner join [' + @db_name + '].sys.schemas as ss on ss.schema_id = st.schema_id where ss.name <> ''sys'' '

exec sp_executesql @n_cmd

-------------------------------------------------------------------------------------------
-- disable constraints

declare @table_name sysname
declare @schema_name sysname
declare @counter_max int
declare @counter int

select @counter_max = max(rec_id) from #temp_tables

if @counter_max is null
set @counter_max = 0

declare @object_name nvarchar(max)

set @counter = @counter_max
while @counter > 0
begin
set @table_name = null
set @schema_name = null

select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter

if @table_name is null or @schema_name is null
break

set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'

set @n_cmd = N'alter table ' + @object_name + N' nocheck constraint all'

print @n_cmd

begin try
exec sp_executesql @n_cmd
end try
begin catch
print '-------------------------------------------------------------------------'
print 'ERROR - Could not disable constraints for table ' + @object_name
print error_message()
print '-------------------------------------------------------------------------'
end catch

set @counter = @counter - 1
end

----------------------------------------------------------------------------------------------
-- delete records from tables

set @counter = @counter_max
while @counter > 0
begin
set @table_name = null
set @schema_name = null

select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter

if @table_name is null or @schema_name is null
break

set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'

set @n_cmd = 'delete ' + @object_name

print @n_cmd

begin try
exec sp_executesql @n_cmd

if @reset_ident = 1
begin
set @n_cmd = 'if exists(select * from [' + @db_name + '].sys.columns where object_id = object_id(''' + @object_name + ''') and is_identity = 1) dbcc checkident(''' + @object_name + ''', reseed, 0)'

print @n_cmd

exec sp_executesql @n_cmd
end
end try
begin catch
print '-------------------------------------------------------------------------'
print 'ERROR - Could not clean table ' + @object_name
print error_message()
print '-------------------------------------------------------------------------'
end catch

set @counter = @counter - 1
end

-----------------------------------------------------------------------------------------------
-- enable constraints

set @counter = @counter_max
while @counter > 0
begin
set @table_name = null
set @schema_name = null

select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter

if @table_name is null or @schema_name is null
break

set @n_cmd = 'alter table [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] with check check constraint all'

print @n_cmd

begin try
exec sp_executesql @n_cmd
end try
begin catch
print '-------------------------------------------------------------------------'
print 'ERROR - Could not enable constraints for table ' + @object_name
print error_message()
print '-------------------------------------------------------------------------'
end catch

set @counter = @counter - 1
end

drop table #temp_tables

---------------------------------------------------------------------------------------------------
-- restore database recovery mode

if @recovery_mode <> 'Simple'
begin
declare @ncmd2 nvarchar(max)

print '-- Restoring database recovery mode'

set @ncmd2 = 'alter database [' + @db_name + '] set recovery ' + @recovery_mode

print @ncmd2

exec sp_executesql @ncmd2
end

end
go

-- example:

exec dbo.proc_clear_database 'AdventureWorks', 1