Your First SQL Server 2000 Database

Creating a Database with Query Analyzer

Ah! We've arrived at the fun part of the tutorial: coding a database and its tables by hand. Don't be scared: the manual creation of databases is not difficult at all and it's a great way to learn the TSQL syntax.

We're going to use Query Analyzer to create exactly the same database that we did with Enterprise Manager, so point to Start menu -> Programs -> Microsoft SQL Server and run Query Analyzer. When it loads, you'll be presented with the login screen. I'm assuming that you've just installed a fresh copy of SQL Server 2000, so the default username of sa and no password is fine for now. Click OK to login to your database and start working with Query Analyzer.

First off, let's delete the MyDatabase1 database that we created earlier with Enterprise Manager. The sysdatabases table in the master database contains entries for each SQL Server 2000 database that exists on our PC. When we created the MyDatabase1 database, a new record was added to the sysdatabases table. Here's how the sysdatabases table looks:

Using some simple TSQL statements, we can check if the MyDatabase1 database exists in the sysdatabases table of the master database. If it does, then we'll delete it. Enter the following code into the Query Analyzer window:

USE MASTER    
GO    
   
-- If the database already exists, drop it    
IF EXISTS(SELECT * FROM sysdatabases WHERE name='MyDatabase1')    
DROP DATABASE MyDatabase1    
GO

Note that lines that start with --- are comments.

In Enterprise Manager we used the tree view in the left pane to choose which database we wanted to work with. In Query Analyzer, we specify the USE [database name] statement to tell SQL Server 2000 which database we'd like to work with. The GO command executes all code up until that specific point of the script, so after the first GO command, SQL Server knows that any commands it encounters should be executed against the master database.

Obviously the MyDatabase1 database already exists. But let's pretend that we don't know it does. By using the SELECT * FROM sysdatabases WHERE name='MyDatabase1'

TSQL command in combination with the IF EXISTS... statement, we set up a condition: if any records are returned from the SELECT query to the sysdatabases table of the master database, then we proceed to execute the statements that are part of the IF block:

DROP DATABASE MyDatabase1    
GO

The DROP DATABASE [database name] statement removes a database from the sysdatabases table and also deletes all of its tables, stored procedures, triggers, etc. We use the DROP DATABASE command to remove the MyDatabase1 database if it exists. At this point we need to flush our TSQL commands to SQL Server, so we use the GO command to do so. We now know for sure that a database called MyDatabase1 doesn't exist.