StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Just set the @schemaID to the name of the schema you wish to blow away and it should do the rest. If you end up with a FK dependency loop it will break and tell you what to do...</p> <pre><code>Declare @schemaID Nvarchar(256) Set @schemaID = 'Schema' -- Set this to the name of the schema you wish to blow away If Object_ID('tempdb..#tables') Is Not Null Drop Table #tables Create Table #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256)) Insert #tables Select Row_Number() Over (Order By s.name, so.name), s.name, so.name From sysobjects so Join sys.schemas s On so.uid = s.schema_id Where so.xtype = 'u' And s.name = @schemaID Declare @SQL Nvarchar(Max), @schema Nvarchar(256), @table Nvarchar(256), @iter Int = 1, @loopCatch Int = 0 While Exists (Select 1 From #tables) Begin Select @schema = SchemaName, @table = TableName From #tables Where tID = @iter If Exists (Select 1 From sysobjects o Join sys.schemas s1 On o.uid = s1.schema_id Join sysforeignkeys fk On o.id = fk.rkeyid Join sysobjects o2 On fk.fkeyid = o2.id Join sys.schemas s2 On o2.uid = s2.schema_id Join #tables t On o2.name = t.TableName Collate Database_Default And s2.name = t.SchemaName Collate Database_Default Where o.name = @table And s1.name = @schema) Begin Update t Set tID = (Select Max(tID) From #tables) + 1 From #tables t Where tableName = @table And schemaName = @schema Set @iter = @iter + 1 End Else Begin Set @Sql = 'Truncate Table [' + @schema + '].[' + @table + ']' Begin Try Exec sp_executeSQL @SQL; Delete t From #tables t Where tableName = @table And schemaName = @schema Set @iter = @iter + 1 End Try Begin Catch Print @SQL Update t Set tID = (Select Max(tID) From #tables) + 1 From #tables t Where tableName = @table And schemaName = @schema Set @iter = @iter + 1 Set @loopCatch = @loopCatch + 1; If @loopCatch > 5 Begin Select 'WARNING: Endless FK redundancy loop. Drop the constraints and these tables, truncate and reapply constraints manually' Union All Select '[' + SchemaName + '].[' + TableName + ']' From #tables; Break; End End Catch End End </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