If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database
After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script
Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your SSMS query window
Here is what the script looks like
As you can see, there are 4 things going on here.
- Remove the constraints
- Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
- Drop XML Schema Collections
- Drop the Schema
Of course if you have a view in another database or schema referencing your objects inside this schema you will have problems...I will get to this later
Running that script will give you the following output
Run that and it should all run fine, the schema should be gone
Now change @SchemaName NVARCHAR(100) = 'HumanResources' to @SchemaName NVARCHAR(100) = 'Sales'
Run the script again, run the output from the script
Finally change the variable to Person @SchemaName NVARCHAR(100) = 'Person'
Run the script, run the output. All 3 schemas we specified should be gone now
Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.
Here is the error you will receive
Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.
However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema
I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing