A SQL script that can be used to generate a script to reset data in a set of database tables. It uses foreign key meta-data to determine the order in which tables can be cleared.

generate-deletes.sql

-- Calculates the dependencies between database tables in a SQL Server database
-- to assist with writing a script to clear data from a set of database tables.

-- This works in SQL Server only. The information schema views follow an ISO 
-- standard and this script could probably be adapted to another platform fairly easily.

declare @crlf varchar(2)
set @crlf = char(13) + char(10)

-- 1. Get the tables that we want to clear data from. We're restricting ourselves
-- to a single schema here. To support multiple schemata we'd need to track 
-- schema names along with table names. Is it common to have foreign key 
-- constraints between tables belonging to different schemata?
declare @Table table (TableName nvarchar(128), Depth int NULL)

insert @Table (TableName)
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
-- Add additional filtering here to exclude certain tables, e.g. read-only 
-- 'reference' data, logs etc


-- 2. Get the dependencies between the tables 
declare @Dependency table (ConstraintName nvarchar(128), FKTableName nvarchar(128), PKTableName nvarchar(128))

insert @Dependency 
select rc.CONSTRAINT_NAME, pkccu.TABLE_Name as PKTableName, fkccu.TABLE_NAME as FKTableName
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fkccu
  on rc.UNIQUE_CONSTRAINT_NAME = fkccu.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkccu
	on rc.CONSTRAINT_NAME = pkccu.CONSTRAINT_NAME
where fkccu.TABLE_NAME in (select TableName from @Table)
	and pkccu.TABLE_NAME in (select TableName from @Table)
	and pkccu.TABLE_SCHEMA = 'dbo'
	and fkccu.TABLE_SCHEMA = 'dbo'

-- 3. Define delete order so that tables are deleted before tables that 
-- they depend on (http://en.wikipedia.org/wiki/Topological_sorting)
declare @Depth int, @Complete bit
set @Depth = 0
set @Complete = 0
while @Complete = 0
begin
	update t
	set Depth = @Depth
	from @Table t
	where [Depth] is null
	and not exists 
		(select 1 from @Dependency r
		inner join @Table t2 on r.FKTableName = t2.TableName
		where PKTableName = t.TableName and t2.Depth is null)
		
	if @@ROWCOUNT = 0 set @Complete = 1
		
	set @Depth = @Depth + 1
end

declare @Warning varchar(max)
set @Warning = 'WARNING: Unable to establish delete order for this table. '
 + 'Possible conflicting constraints are listed in the next result set.'


-- 4. Select our results. 
select TableName, 
	Depth, 
	case when Depth is null then @Warning else '' end as Notes,
	'delete from [dbo].[' + TableName + ']' as DeleteStatement
from @Table
order by ISNULL(Depth, -1), TableName

-- 5. Select foreign keys that prevent us from ordering tables
if exists (select 1 from @Table where Depth is null)
	select *, 
	case 
		when PKTableName = FKTableName then 'Self-referencing foreign key constraint'
		when exists(select * from @Dependency d2 where d2.PKTableName = d.FKTableName and d2.FKTableName = d.PKTableName) then 'Mutually dependent foreign key constraints on these tables'
		else ''
	end as Warning
	from @Dependency d
	where PKTableName in (select TableName from @Table where Depth is null)
		and FKTableName in (select TableName from @Table where Depth is null)
order by Warning desc

-- 6. What you do from here is up to you. If you have a simple schema you could 
-- execute the delete statements directly:

/*
if not exists (select 1 from @Table where Depth is null)
begin
	declare @sql varchar(max)
	set @sql = ''
	select @sql=@sql+'delete from [dbo].[' + TableName + ']' + @crlf
	from @Table
	order by Depth, TableName
	print 'Executing SQL:' + @crlf + @sql
	execute(@sql)
end
*/

-- In more complex cases you might need to copy the delete statements from 
-- the results window and add some additional filtering or deal manually with 
-- self-referencing constraints etc.