Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Auto generate change scripts in SQL Server Management Studio SSMS for tables

MSSQLTips author Atif Shehzad By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Studio

Problem
As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?  This tip shows you an option that exists within SSMS to automatically generate these scripts for all table changes when using the table designer.

Solution
Within SQL Server Management Studio (SSMS) there is an option to
automatically generate scripts for your changes done through the GUI.  The solution for this problem is provided in SSMS and works with both SQL Server 2005 and SQL Server 2008, but by default this option that we will discuss is disabled.

To enable the option

  • From the SSMS menus click on "Tools" 
  • Click on "Options..."
  • Click on "Designers"
  • Check the checkbox "Auto generate change scripts" as highlighted below

After checking the specified checkbox, SSMS is now able to generate scripts for the creation and/or modification of any table when you use the designer.

Testing It Out

Let's confirm the script generation for the creation of a new table.

  • In SSMS select a database and expand the tree
  • Right click on the tables folder inside and click on "New Table".
  • A designer will appear for the creation of new table.
  • Create a single column for this table, I have created a column named TestCol1 as nchar(10)
  • Save the new table and give the table a name and click OK
  • Another window will popup that includes the actual script for creating this new table as shown below

  • Click on "Yes" to save the script and you will be prompted to specify the location.  The file name by default will be the name of the table such as "tableName.sql", but this can be changed to anything you like.
  • If you do not want to save the script then click "No", clicking "No" will not roll back the implemented changes, but the script will not be saved

Notes:

  • It is important to mention here that unchecking the box in this frame as highlighted above "Automatically generate change script on every save" will cause this change to be undone in the options that we first configured.
  • Also scripts are not automatically generated for deletion of a table
  • While working on views through designers no script is generated, however the code for views operation is provided inside the designer and may be copied and saved.

Next Steps

  • By implementing script auto generation for your SSMS designer you will be able to save changes made through designers easily, which will allow you to have change scripts for changes made through SSMS designer.


Last Update: 4/6/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, April 06, 2009 - 10:23:03 AM - DavidB Read The Tip
Nice tip. It is easy to forget that SSMS can generate schema changes for execution later on.

Wednesday, February 29, 2012 - 10:19:12 PM - irv Read The Tip

consider this script from ssms that changes a table

 

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

USE [test]

GO

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_test_errors

        (
        test1 nchar(10) NULL,
        test2 nvarchar(15) NULL,
        added_field nchar(10) NULL
        )  ON [PRIMARY]
GO

 

ALTER TABLE dbo.Tmp_test_errors SET (LOCK_ESCALATION =TABLE)
GO

 

IF EXISTS(SELECT * FROM dbo.test_errors)
         EXEC('INSERT INTO dbo.Tmp_test_errors (test1, test2)
                SELECT test1, CONVERT(nvarchar(15), test2) FROM dbo.test_errors WITH (HOLDLOCK TABLOCKX)')
GO


DROP TABLE dbo.test_errors
GO


EXECUTE sp_rename N'dbo.Tmp_test_errors', N'test_errors','OBJECT'
GO

COMMIT

 

Let's say there's an error on insert.  This script will back out of the insert batch but will run the drop table batch!  Have you seen a "best practices" way of modifying these design view generated script to preven tthis?


Thursday, March 01, 2012 - 4:28:09 AM - Atif shehzad Read The Tip

@irv- Yes that may be the case as you mentioned. I would point out a couple of points

This tip is meant to enable you to store the changes performed through designer as you store changes made through T-SQL. It is just for tracking the changes.

However If you are ever interested in executing the generated script then plz consider the warning in the first line of script. It is as under

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

You shoud analyze and change the script for avoiding any data or object loss. Although chances are minimal for scenario that u mentioned. However proper measurements should be ensured as instrcuted.

Thanks


Friday, June 29, 2012 - 3:07:53 PM - Rafay Read The Tip

hi want to compare and update the schema automaticaly without any third party tool in sql server 2005 i am new in the feild and i cant find any thing without thirdparty tool on google can you guide me in it... Waiting for your reply..

Regards,

Rafay..


Monday, July 02, 2012 - 2:35:46 AM - Atif Read The Tip

@Rafay. You may generate the tables, columns, or views by using the tables/views like INFORMATION_SCHEMA.TABLES , sys.columns etc. Once compared through the scripts you may modify the objects as required. Also have a look at following couple of helpful links

http://www.mssqltips.com/sqlservertip/2089/sql-schema-comparison-with-visual-studio-2010/

http://msdn.microsoft.com/en-us/library/dd193250.aspx

Thanks



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.