StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="http://www.jasinskionline.com/TechnicalWiki/List-Tables-in-Dependency-Order-SQL-Server.ashx" rel="nofollow noreferrer">This article</a> gives a good idea of how to do what you're asking.</p> <p>EDIT: I've modified the original query given in the link to:</p> <ol> <li>Make the script schema aware</li> <li>Correct the bug noted in the comments below</li> </ol> <p>Not sure why the editor is doing such a poor job of formatting the code block.</p> <pre><code>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 order by 1 desc ,2 desc </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload