declare @RootTable nvarchar(1000) ,@RootColumn nvarchar(1000) ,@FilterClause nvarchar(1000) select @RootTable = 'Tenant' ,@RootColumn = 'Id' ,@FilterClause = 'in (10)' ;with MyRecursion as ( /*--- Base Case ---*/ SELECT TableName = [name] ,Lvl = 1 ,WhereClause = convert(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause) ,ParentTable = convert(nvarchar(1000), null) ,DepPath = convert(varchar(8000), '/' + [name]) FROM sys.tables where 1=1 and [name] = @RootTable /*--- Recursive Case ---*/ union all SELECT TableName = f.OnTable ,Lvl = r.Lvl + 1 ,WhereClause = convert(nvarchar(4000), case r.Lvl when 1 THEN ' where [' + f.OnColumn + '] ' + @FilterClause else ' where [' + f.OnColumn + '] in (select [' + f.AgainstColumn + '] from [' + f.AgainstTable + '] ' + r.WhereClause + ')' end ) ,ParentTable = convert(nvarchar(1000), f.AgainstTable) ,DepPath = convert(varchar(8000), r.DepPath + '/' + f.OnTable ) FROM dbo.FKeys f inner join MyRecursion r on f.AgainstTable = r.TableName /* TODO: Exclude entities we want to keep */ and f.OnTable not in ('AspNetUsers','AspNetRoles') /* Avoid circular references */ and r.DepPath not like '%/' + f.OnTable + '/%' ) ,DeleteStatements as ( SELECT Lvl = max(Lvl) ,TableName ,SqlCode = 'delete from [' + TableName + ']' + WhereClause FROM MyRecursion WHERE 1=1 and TableName not in ('Lab') group BY TableName ,WhereClause ) select * --distinct TableName, Lvl FROM DeleteStatements order by Lvl desc ,TableName
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.