CODEWITHSUNDEEP
sql-serverdatabase
Dónal
Dónal

Reputation: 187549

drop SQL Server schema

I understand that you cannot simply drop an SQL server schema, you must first of all drop all the objects contained therein. I found this stored proc that performs the task of dropping all objects and then the schema itself.

Is there really no simpler way to drop a schema? Ideally, I'd like to find a way to do this without using a stored proc.

Also, it seems like the stored proc will cause errors if the schema name provided does not exist. I would like it to simply do nothing instead. I guess this is simply a matter of putting this pseudocode at the top of the script

IF @SchemaName NOT EXISTS
    QUIT

Can someone convert this into language that SQL Server will understand?

Upvotes: 4

Views: 2036

Answers (4)

gbn
gbn

Reputation: 432331

You have to remove all objects in the schame before dropping it or migrate all objects to a new schema. There is no "wildcard" option for either

To exit a stored procedure before any further processing...

IF SCHEMA_ID(@SchemaName) IS NULL
    RETURN

Upvotes: 2

Martin
Martin

Reputation: 40345

The following at the top of the script should help:

IF SCHEMA_ID(@SchemaName) IS NULL 
    RETURN

SCHEMA_ID returns the schema ID associated with a schema name, and RETURN exits unconditionally from a query or procedure.

Upvotes: 4

Mark PM
Mark PM

Reputation: 2919

You must drop all objects before dropping the schema. To check if a schema exists:

IF NOT EXISTS (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='YOUR DB NAME HERE' and SCHEMA_NAME=@SchemaName)
BEGIN
     -- Do some processing...
     return
END

Upvotes: 0

MrEdmundo
MrEdmundo

Reputation: 5165

if exists(select * from sys.schemas where name = @SchemaName)
begin
    -- Your work
end

Upvotes: 0

Related Questions