Automated scripting of SQL Server schema changes

Question

I'm working on a project where we are updating client databases from older version to newer versions of the database and the related schema.

I'm looking for a way do the following for all of the tables in the database that our program uses.

If the table doesn't exist
  create table
else
  for each column in the table
    bring the column up to date or create it

I can't seem to find a way to do this through management studio and I haven't turned anything up through Google.

Something that would either give me this option in SQL Management Studio or a program that would generate the scripts for me would be good.

Thanks.

Answer 1

There are two main options for you

Redgate's SQL Compare (awesome tool)

MS Visual Studio for Database Developers.

Answer 2

http://www.red-gate.com/products/SQL_Compare/

It can automatically compare and synchronize database schemas or create scripts to update one database to match the schema of another

Answer 3

For a home grown option you can always look to this blog for suggestions: http://www.tewari.info/dbupdater/

Basically after creating and setting up your baseline database, you can add your scripted out changes to a directory and run this application. The app will ignore any changes already present, bring your database objects up to the latest and greatest. You can also include 'post processing' functionality if you need to create data or do anything else after the schema structure has been updated.

The downside is that its manual but it does give you a very good level of control over things.

Answer 4

You can write a procedure to generate code for this schenario by using the INFORMATION_SCHEMA views. Its Free :) !

DECLARE @Table_of_Tables TABLE
(
   Id int,
   Table_name varchar(50)
)
DECLARE @tablename varchar(50)
DECLARE @iterator int
DECLARE @count int
DECLARE SQL nvarchar(max)

SET @SQL = ''

INSERT INTO @Table_of_Tables 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

SELECT @count = COUNT(1) FROM @Table_of_Tables 
SET @iterator = 1

WHILE(@iterator  <= @Count)
BEGIN
    SET @SQL = @SQL + ' IF EXISTS(SELECT 1 FROM sys.sysobjects where type = ''u'' and name = ''+ @Table_of_Tables +'''      SET @SQL = @SQL + ' BEGIN '
    SET @SQL = @SQL + <CreateTableScriptHere> 
    SET @SQL = @SQL + ' END '
    SET @SQL = @SQL + ' ELSE '
    SET @SQL = @SQL + ' BEGIN '
    -- Similar Loop to generate code for all the columns in this table
    -- SELECT COLUMN_NAME FROM INFROMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename
    SET @SQL = @SQL + ' END '

    SET @iterator = @iterator + 1
END