SELECT 'TRUNCATE TABLE '+ QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME) TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%DETAIL'

;with    cteTbHierarchy 
          as ( /* Select all table without (selfreferencing) FK */
               select distinct
                        1 as LevelID
                      , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
                      , Parent.name as TableName
                      , Parent.object_id as TbObjID
               from     sys.objects Parent
               left join sys.foreign_key_columns RefKey
                        On RefKey.parent_object_id = Parent.object_id
                           and RefKey.parent_object_id <> RefKey.referenced_object_id
                           and RefKey.constraint_column_id = 1
               where    RefKey.parent_object_id is null
                        and Parent.type = 'U'
                        and Parent.name <> 'dtproperties'
               UNION ALL
               /* add tables that reference the anchor rows */
               SELECT   H.LevelID + 1
                      , OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
                      , OBJECT_NAME(Parent.object_id) as TableName
                      , Parent.object_id as TbObjID
               from     sys.objects Parent
               inner join sys.foreign_key_columns RefKey
                        On RefKey.parent_object_id = Parent.object_id
                           and RefKey.parent_object_id <> RefKey.referenced_object_id
                           and RefKey.constraint_column_id = 1
               inner join cteTbHierarchy H
                        on H.TbObjID = RefKey.referenced_object_id 
               where    Parent.type = 'U'
                        and Parent.name <> 'dtproperties'
             )
    select  distinct LevelID, TableOwner, TableName
    from    cteTbHierarchy
    WHERE LevelID!=1
    order by LevelID desc -- descending order = order of row deletes
          , TableOwner
          , TableName ;

with Fkeys as (

    select distinct

         OnTable       = onTableSchema.name + '.' + OnTable.name
        ,AgainstTable  = againstTableSchema.name + '.' + AgainstTable.name 

    from 

        sysforeignkeys fk

        inner join sys.objects onTable 
            on fk.fkeyid = onTable.object_id
        inner join sys.objects againstTable  
            on fk.rkeyid = againstTable.object_id

        inner join sys.schemas onTableSchema 
            on onTable.schema_id = onTableSchema.schema_id

        inner join sys.schemas againstTableSchema 
            on againstTable.schema_id = againstTableSchema.schema_id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and onTableSchema.name + '.' + OnTable.name <> againstTableSchema.name + '.' + AgainstTable.name
    )

,MyData as (

    select 
         OnTable = s.name + '.' + o.name
        ,AgainstTable = FKeys.againstTable

    from 

        sys.objects o
            inner join sys.schemas s
                on o.schema_id = s.schema_id

        left join FKeys
            on  s.name + '.' + o.name = FKeys.onTable
        left join Fkeys fk2
            on s.name + '.' + o.name = fk2.AgainstTable
                and fk2.OnTable = Fkeys.AgainstTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
        and fk2.OnTable is null
    )

,MyRecursion as (

    -- base case
    select 
         TableName    = OnTable
        ,Lvl        = 1
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)

select 
     Lvl = max(Lvl)
    ,TableName
    ,strSql = 'delete from ' + tablename + ' ; '
from 
    MyRecursion
group by
    TableName
HAVING max(Lvl) >1
order by 
     1 desc
    ,2 desc;