Geek Answers Handbook

Delete SQL Server Schema

I understand that you cannot just abandon the SQL server schema, you must first delete all the objects contained in it. I found this saved proc that performs the task of deleting all the objects, and then the schema itself.

Is there an easier way to abandon the circuit? Ideally, I would like to find a way to do this without using a stored procedure.

Also, it seems that the stored proc will cause errors if the specified schema name does not exist. I would like him to just do nothing. I assume this is just a matter of having this pseudo code at the top of the script

IF @SchemaName NOT EXISTS QUIT

Can someone convert this to a language that SQL Server will understand?

+4
source share
4 answers

You must delete all the objects in the slide before you drop it or move all the objects to a new layout. There is no "wildcard" option for

To exit a stored procedure before further processing ...

 IF SCHEMA_ID(@SchemaName) IS NULL RETURN
+2
source

The following at the top of the script should help:

 IF SCHEMA_ID(@SchemaName) IS NULL RETURN

SCHEMA_ID returns the schema identifier associated with the schema name, and RETURN unconditionally exits the request or procedure.

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

You must delete all objects before resetting the circuit. 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
0
source

More articles:


All Articles