SQL Script to Create and Update a Database

This script is specific to SQL Server. It uses some syntax that only works in SQL Server (2000,2005,2008). This is a really convenient way to keep your database schema under source control by creating and managing your database in script. Then you can check-in this code as a .SQL file.

One easy way to do this is to have 2 separate files. One for creating the base database and the other for changes after the initial schema design. So the 1st script would contain creation of tables and foreign key relationships. The 2nd script would contain changes with a comment/note next to each to add/remove tables, add/remove fields in a table, and change relationships.

What is specifically useful about these scripts is that they can be run multiple times without causing errors in your query analyzer. Each change is wrapped in an IF block that makes sure the change has not already been run. This makes it really easy to insure your database is up-to-date but just re-rerunning the script whenever a change is made. It also let's you merge changes from multiple users since the script is checked in so you never lose a change.

Script 1: Database Schema Setup Script

The first script would contain the base database schema and table creation. Here's an example. First, create the database if it hasn't already been created. This script will check if the database table exists before creating it.

-- create the database if it does not already exist

if not exists(select * from sys.databases 

where name = 'MyDatabaseName')

begin

    create database MyDatabaseName

end

Now create the tables. Note that this script checks if the table exists before creating it so it can be run multiple times:

-- specify that all subsequent sql commands use this database

use [MyDatabaseName]

-- add UserAccount table if it does not already exist

if not exists (select * from information_schema.columns

where table_name = 'UserAccount')

begin

    create table UserAccount 

    (

        Id int identity(1000,1) not null,

        UserName nvarchar(50) not null,

        [Password] nvarchar(20) null,

        DateUpdated datetime not null,

        DateCreated datetime not null,

        constraint PK_UserAccount primary key (Id),

    )    

end

-- add Category table if it does not already exist

if not exists (select * from information_schema.columns

where table_name = 'Category')

begin

    create table Category 

    (

        Id int identity(1000,1) not null,

        UserAccountFk int not null,

        CategoryName nvarchar(50) not null,

        CategoryCode nvarchar(20) null,

        DateCreated datetime not null,

        constraint PK_Category primary key (Id),

    )    

end

Now we can add a foreign key constraint from the Category table to the UserAccount primary key. Again, this script checks if the foreign key already exists before creating it.

-- add foreign key constraint for Category/UserAccount table

if not exists(select * from information_schema.table_constraints

where table_name = 'Category'

and constraint_name = 'FK_Category_UserAccount')

begin

    -- add foreign key constraint to UserAccount table for Category

    alter table Category  with check add constraint FK_Category_UserAccount foreign key(UserAccountFk)

    references UserAccount (Id)

    -- set up a check constraint on this foreign key for this table

    alter table Category check constraint FK_Category_UserAccount

end

Script 2: Database Changes Script

Now we need a script to keep track of our changes to this initial schema in the first script as we update and change our database over time. You could have this all in one script but it is easier to manage and maintain if it is separated. Here we add a new field to a table and drop an existing field from a table as examples.

-- 11/11/2008 - Added Email field to UserAccount table to track user's email address.

if not exists (select * from information_schema.columns

where table_name = 'UserAccount' 

and column_name = 'Email')

begin

    alter table UserAccount add Email nvarchar(250) null

end

-- 11/12/2008 - Removed CategoryCode field from Category table.  No longer used by the system.

if exists (select * from information_schema.columns

where table_name = 'Category' 

and column_name = 'CategoryCode')

begin

    alter table Category drop column CategoryCode

end

The only last piece of this puzzle that might be useful is that you might want to insert some data in the table. This is especially useful for lookup tables or for adding sample testing data to help with development.



(Optional) Script 3: Populate Test or Lookup Data

Here's an optional 3rd script to insert some basic categories in the Category table.

-- insert a user into the UserAccount table

if not exists (select * from UserAccount

where UserAccount.UserName = 'test'

and UserAccount.Password = 'test')

begin

    insert into UserAccount (UserName, [Password], DateUpdated, DateCreated)

    values ('test', 'test', getdate(), getdate())

end

-- insert categories into Category table

if not exists (select * from Category

where Category.UserAccountFk = 1000

and Category.CategoryName = 'BioTech')

begin

    insert into Category (UserAccountFk, CategoryName, DateCreated)

    values (1000, 'BioTech', getdate())

end

Now just put these files in your Solution/Project in a Data folder or something that is easy to keep track of. I like putting these scripts in the App_Data folder if I am using an ASP.NET web project. Then you can check-in/out these files to manage your entire database and make sure multiple developers have the exact same schema to work from. You can download all of this script as a file here:

create_database.sql

update_database.sql

populate_data.sql

One other thing that is really useful when creating and updating your tables is to add an extended property "description" to each table's column. Then you can write a useful description of the column so you can document it "in database". Very useful. To do this, you can read the follow-up blog here:

SQL Create Table Add Description to Column

The scripts contain all the code above and are ready if you wanted to run them in your database for testing.