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.

In that case the Redgate products (SQLCompare, SQL DataCompare or SQL Packager) should do what you need then. If not explain what they don't do so we can advise better. — Martin Smith
– Martin Smith, Commented Jul 7, 2010 at 19:51
@Martin Smith - I'm pretty familiar with the tools, they just don't do exactly what I need them to. — msarchet
– msarchet, Commented Jul 7, 2010 at 21:57

Baaju · Accepted Answer · 2010-07-07 18:41:39Z

1

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

answered Jul 7, 2010 at 18:41

Baaju

2,0202 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

msarchet Over a year ago

I figured you could do something like this.

msarchet Over a year ago

This is the closest anyone has came to what I needed but I ended up using codesmith to generate my scripts.

ChrisLively · Accepted Answer · 2010-07-07 18:04:11Z

0

There are two main options for you

Redgate's SQL Compare (awesome tool)

MS Visual Studio for Database Developers.

answered Jul 7, 2010 at 18:04

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

2 Comments

msarchet Over a year ago

Yes, SQL Compare is something that I have looked at but it can't do what I need it to do. Could you explain more on the VS option

ChrisLively Over a year ago

Exactly what doesn't it do? You can do data and or schema compares with it... The visual studio option is built in to visual studio 2010 premium or ultimate. It also does schema and data compares.

Ed B · Accepted Answer · 2010-07-07 18:04:53Z

0

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

answered Jul 7, 2010 at 18:04

Ed B

6,0543 gold badges29 silver badges35 bronze badges

Comments

Christopher Klein · Accepted Answer · 2010-07-07 18:09:41Z

0

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.

answered Jul 7, 2010 at 18:09

Christopher Klein

2,8035 gold badges40 silver badges65 bronze badges

Collectives™ on Stack Overflow

Automated scripting of SQL Server schema changes

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related