Last active
December 10, 2015 20:28
-
-
Save danmalcolm/4488495 to your computer and use it in GitHub Desktop.
A sql script to help automate resetting data in a set of database tables. It uses foreign key meta-data to determine the order in which tables can be cleared.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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. | |
-- 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.' | |
-- 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 | |
-- Select foreign keys that prevent us from ordering tables | |
if exists (select 1 from @Table where Depth is null) | |
select * from @Dependency | |
where PKTableName in (select TableName from @Table where Depth is null) | |
and FKTableName in (select TableName from @Table where Depth is null) | |
-- What you do from here is up to you. If you have a simple schema you could | |
-- execute the delete statements directly using sp_executesql. | |
-- In more complex cases you might need to copy the delete statements from | |
-- the results window and some additional filtering or deal manually with | |
-- self-referencing constraints. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment