Skip to content

Instantly share code, notes, and snippets.

@drunkcod
Created October 5, 2014 19:43
Show Gist options
  • Save drunkcod/918ae6f27ecf2fe3087f to your computer and use it in GitHub Desktop.
Save drunkcod/918ae6f27ecf2fe3087f to your computer and use it in GitHub Desktop.
SqlServer - list tables in delete/truncate order.
with tab(object_id, level, ref_id) as (
select t.object_id, 0, null
from sys.tables t
where t.type = 'U'
union all
select fk.parent_object_id, level + 1, t.object_id
from tab t
inner join sys.foreign_keys fk on fk.referenced_object_id = t.object_id
)
select
TableName = object_name(object_id)
,CanTruncate =
case when exists(select null from sys.sql_dependencies
where exists(select null from sys.objects where referenced_major_id = tab.object_id and type = 'V'))
then 0 else 1
end
from (
select object_id, level = max(level)
from tab
group by object_id) tab
order by level desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment