John Soer

Archive for July, 2010

It is not very common to create a database via scripts or modify a database via scripts, most often the changes are done via a designer which does make the job a lot easier.

In some cases it is worthwhile to do it via a script one of the instances where I use scripts a lot is when I make changes to a development database and I know that I have to make same changes to a production database at some point.

Below is a list of the most common alter statements to modify the structure of  a database

• Add a column to a database

  
  ALTER TABLE table_name
  ADD column_name datatype
  

  • Add a primary key to a database

  
  ALTER TABLE table_name
  ADD CONSTAINT constraint_name
  PRIMARY KEY (field_name)
  

  • Add an index to a table

  
  CREATE NONCLUSTERED INDEX index_name
  ON table_name (field_name ASC)
  

  • Add a foreign constraint to a table

  
  ALTER TABLE table_name
  WITH CHECK ADD CONSTRAINT contraint_name
  FOREIGN KEY (field_name)
  REFERENCES table_name (field_name)
  
  ALTER TABLE table_name
  CHECK CONSTRAINT constraint_name
  

  Note that there are some very good tools out there as well to determine the structural changes between a development and production database but you may not always have access to these tools.