CODEWITHSUNDEEP
sqlsql-serverdatabase-schema
Captain Alizee
Captain Alizee

Reputation: 137

Drop all objects in SQL Server database that belong to different schemas?

Is there a way to drop all objects in a db, with the objects belonging to two different schemas?

I had been previously working with one schema, so I query all objects using: 

Select * From sysobjects Where type=...

then dropped everything I using 

Drop Table ...

Now that I have introduced another schema, every time I try to drop it says something about I don't have permission or the object does not exist. BUT, if I prefix the object with the [schema.object] it works. I don't know how to automate this, cause I don't know what objects, or which of the two schemas the object will belong to. Anyone know how to drop all objects inside a db, regardless of which schema it belongs to?

(The user used is owner of both schemas, the objects in the DB were created by said user, as well as the user who is removing the objects - which works if the prefix I used IE. Drop Table Schema1.blah)

Upvotes: 8

Views: 19412

Answers (4)

Haseeb
Haseeb

Reputation: 990

try this with sql2012 or above,

this script may help to delete all objects by selected schema Note: below script for dbo schema for all objects but you may change in very first line @MySchemaName

DECLARE @MySchemaName VARCHAR(50)='dbo', @sql VARCHAR(MAX)='';
DECLARE @SchemaName VARCHAR(255), @ObjectName VARCHAR(255), @ObjectType VARCHAR(255), @ObjectDesc VARCHAR(255), @Category INT;

DECLARE cur CURSOR FOR
    SELECT  (s.name)SchemaName, (o.name)ObjectName, (o.type)ObjectType,(o.type_desc)ObjectDesc,(so.category)Category
    FROM    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sysobjects so ON so.name=o.name
    WHERE s.name = @MySchemaName
    AND so.category=0
    AND o.type IN ('P','PC','U','V','FN','IF','TF','FS','FT','PK','TT')

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category

SET @sql='';
WHILE @@FETCH_STATUS = 0 BEGIN    
    IF @ObjectType IN('FN', 'IF', 'TF', 'FS', 'FT') SET @sql=@sql+'Drop Function '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('V') SET @sql=@sql+'Drop View '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('P') SET @sql=@sql+'Drop Procedure '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('U') SET @sql=@sql+'Drop Table '+@MySchemaName+'.'+@ObjectName+CHAR(13)

    --PRINT @ObjectName + ' | ' + @ObjectType
    FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category
END
CLOSE cur;    
DEALLOCATE cur;
SET @sql=@sql+CASE WHEN LEN(@sql)>0 THEN 'Drop Schema '+@MySchemaName+CHAR(13) ELSE '' END
PRINT @sql
EXECUTE (@sql)

Upvotes: 3

JonnyRaa
JonnyRaa

Reputation: 8038

Neither of the other questions seem to have tried to address the all objects part of the question.

I'm amazed you have to roll your own with this - I expected there to be a drop schema blah cascade. Surely every single person who sets up a dev server will have to do this and having to do some meta-programming before being able to do normal programming is seriously horrible. Anyway... rant over!

I started looking at some of these articles as a way to do it by clearing out a schema: There's an old article about doing this, however the tables mentioned on there are now marked as deprecated. I've also looked at the documentation for the new tables to help understand what is going on here.

There's another answer and a great dynamic sql resource it links to.

After looking at all this stuff for a while it just all seemed a bit too messy.

I think the better option is to go for

ALTER DATABASE 'blah' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
drop database 'blah'

create database 'blah'

instead. The extra incantation at the top is basically to force drop the database as mentioned here

It feels a bit wrong but the amount of complexity involved in writing the drop script is a good reason to avoid it I think.

If there seem to be problems with dropping the database I might revisit some of the links and post another answer

Upvotes: 4

Gaston Flores
Gaston Flores

Reputation: 2467

I do not know wich version of Sql Server are you using, but assuming that is 2008 or later, maybe the following command will be very useful (check that you can drop ALL TABLES in one simple line):

 sp_MSforeachtable "USE DATABASE_NAME DROP TABLE ?"

This script will execute DROP TABLE .... for all tables from database DATABASE_NAME. Is very simple and works perfectly. This command can be used for execute other sql instructions, for example:

sp_MSforeachtable "USE DATABASE_NAME SELECT * FROM ?"

Upvotes: 0

Bryan
Bryan

Reputation: 17693

Use sys.objects in combination with OBJECT_SCHEMA_NAME to build your DROP TABLE statements, review, then copy/paste to execute:

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.objects
 WHERE type_desc = 'USER_TABLE';

Or use sys.tables to avoid need of the type_desc filter:

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.tables;

SQL Fiddle

Upvotes: 5

Related Questions