Using the catalog views : clearing out a "botched" schema

AaronBertrand — Thu, 07 Jul 2011 15:16:00 GMT

A user on StackOverflow had populated a custom schema with a bunch of tables, and needed to replace them with a new set of tables due to various changes. But he couldn't just drop them all in any arbitrary order, because there were foreign keys in place. His workaround was to just run a "drop all tables" script multiple times until there were no more errors. As you can imagine, on a complicated enough schema, this can be really tedious. Even on a small schema, this just doesn't feel like the right way.

Let's assume the following case:

USE [tempdb];
GO

CREATE SCHEMA [blat] AUTHORIZATION [dbo];
GO

CREATE TABLE [blat].A
(
   id INT PRIMARY KEY
);

CREATE TABLE [blat].B
(
   id INT PRIMARY KEY FOREIGN KEY 
       REFERENCES [blat].A(id)
);

CREATE TABLE [blat].C
(
   id INT FOREIGN KEY 
       REFERENCES [blat].B(id)
);

-- let's also create a table in another schema
-- that points at tables in this schema:

CREATE TABLE [dbo].D
(
   id1 INT FOREIGN KEY 
       REFERENCES [blat].A(id),
   id2 INT FOREIGN KEY 
       REFERENCES [blat].B(id)
);
GO

Initially if you try this:

DROP TABLE [blat].A;

You get this error message:

Msg 3726, Level 16, State 1, Line 1
Could not drop object 'blat.A' because it is referenced by a FOREIGN KEY constraint.

So I put together a quick script that would allow you to drop all of the tables in the schema, by first removing all of the foreign key constraints. In this case order within each set of statements is unimportant, so we can use lazy string concatenation instead of more complicated FOR XML queries that allow you to dictate order:

DECLARE 
   @schema_to_empty SYSNAME = N'blat',
   @sql              NVARCHAR(MAX) = N'';

-- drop all references to tables in the blat schema,
-- even FKs on tables in other schemas.

SELECT @sql += N'
	ALTER TABLE '
   + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(k.parent_object_id))
   + ' DROP CONSTRAINT ' + QUOTENAME(k.[name]) + ';'
FROM sys.foreign_keys AS k
INNER JOIN sys.tables AS t
ON k.referenced_object_id = t.[object_id]
WHERE t.[schema_id] = SCHEMA_ID(@schema_to_empty);

-- then drop all the tables.

SELECT @sql += N'
	DROP TABLE ' 
   + QUOTENAME(@schema_to_empty) + '.'
   + QUOTENAME([name]) + ';'
   FROM sys.tables
   WHERE [schema_id] = SCHEMA_ID(@schema_to_empty);

-- if the output is < 8K, you can inspect it using PRINT:

PRINT @sql;

-- in case it's too big for PRINT (> 8K), but still less than 64K
-- you can run this in grid mode, click on the output, and copy the
-- script from the new window that is created:

-- SELECT CONVERT(XML, @sql);

-- or you can just trust me and run it, MWAHAHAHAHA!:

-- EXEC sp_executeSQL @sql;

You may also want / need to clear out other objects, such as views that reference objects in this schema, or triggers on tables in other schemas that reference objects in this schema. Those bring about other complications I'll address in a future post, if there is enough interest.

In the meantime, wouldn't it be great if we had CREATE OR REPLACE functionality that would, in addition to preventing us from having to drop all the objects first or write complex existence checks and alter commands, also sever these dependencies for us? You can vote for this functionality at Connect #127219.

It would also be nice to have a DROP SCHEMA command that did the same type of thing as I manually do above. Instead of just complaining that the schema is not empty, I'd love to see a DROP SCHEMA ... WITH FORCE option, or something similar, where it took care of all the objects for you - much like DROP DATABASE does. Of course this would be easy to implement in a simple case, but not when schemas have cross-dependencies and references that are embedded in module definitions rather than straightforward DRI and similar constructs.

Search results matching tags 'Administration' and 'catalog views'

Using the catalog views : clearing out a "botched" schema