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

By: Atif Shehzad   |   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

Atif is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips

We Recommend

• More SQL Server DBA Tips...

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 Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. Get your SQL Server database under version control now! Find out why... What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. The COMPLETE Performance Solution for SQL Server - SQL Sentry Free Webinar - Backup compression and storage deduplication: A perfect match?