Intermediate SQL – Post 9 – DROP Column, Table, and Database

For this little lesson, we will create a database, a couple of tables, add some data to those tables, alter them, query them, and then drop them.

I’ll get to the syntax of each of those as we go here, but for the CREATE and ALTER portions, you already know from previous lessons – so here we go.

CREATE Database Linux
GO
 
USE Linux
GO
 
CREATE TABLE Distributions
(
ID			int PRIMARY KEY IDENTITY NOT NULL,
Name			varchar (255),
ReleaseDate		date,
VersionNumber	        varchar (16),
CodeName		varchar (25)
)
GO
 
SET NOCOUNT ON
 
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/10/2012', '12.10', 'Quantal')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/04/2012', '12.04', 'Precise')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','10/01/2011', '11.10', 'Oneiric')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','12/10/2012', '14', 'Nadya')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','11/04/2012', '13', 'Maya')
GO
 
ALTER TABLE Distributions
DROP COLUMN ReleaseDate
GO
 
DROP TABLE Distributions
GO
 
USE msdb
GO
 
DROP DATABASE Linux
GO

Alright – so with that, we created a table and then added some random stuff to that table. After that we altered the table and dropped the column called “Release Date”. Note that dropping a column removes the entire column and all data in it. This is different than a delete statement. A delete statement to remove the values from one row or to make every single row NULL for a particular column is a totally different query. This removes the entire column and all data in it, so that our table no longer has that column. If you wanted to delete a single column from a row, you would do this:

DELETE ReleaseDate
FROM Distributions
WHERE ID = 3

If you wanted to set all columns to NULL, you could do this:

DELETE ReleaseDate
FROM Distributions

Neither of these delete the column however, it just sets all the data in them to NULL – if you want to remove an entire column, you need to DROP it using ALTER TABLE.

The next one you see is that we dropped the table. This means that the entire table gets removed from the database. Once again, it’s not DELETE. It’s DROP TABLE.

The last drop you see is to DROP DATABASE. This one in particular you have to be careful with (for obvious reasons). Firstly, no one can be using it. If they are using it, you’ll get a warning. Since I assumed that you created this database and you are the only one using it, I just decided to USE msdb, and then drop it. If there are other open connections on that database, you need to set it offline to drop it.

ALTER DATABASE Linux
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
 
DROP DATABASE Linux
GO

Just be warned – this will force disconnect anyone who is connected and then drop the database. This means deleting all tables and data in those tables. If you don’t have a backup, all that data is gone. So once again, be extremely careful with this. Ask your system admin if you’re not comfortable with it, and if it’s your own server, then use your own judgement.