Auto Generate Change Scripts in SSMS for Tables

By:   |   Comments (5)   |   Related: > SQL Server Management Studio


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.


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
auto generate change scripts

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
save change script
  • 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


  • 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 2, 2012 - 2:35:46 AM - Atif Back To Top (18289)

@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


Friday, June 29, 2012 - 3:07:53 PM - Rafay Back To Top (18270)

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..



Thursday, March 1, 2012 - 4:28:09 AM - Atif shehzad Back To Top (16224)

@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.


Wednesday, February 29, 2012 - 10:19:12 PM - irv Back To Top (16221)

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]



CREATE TABLE dbo.Tmp_test_errors

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




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)')

DROP TABLE dbo.test_errors

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



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?

Monday, April 6, 2009 - 10:23:03 AM - DavidB Back To Top (3141)
Nice tip. It is easy to forget that SSMS can generate schema changes for execution later on.

get free sql tips
agree to terms