Auto Generate Change Tables Scripts in SQL Server Management Studio

Auto Generate Change Scripts in SSMS for Tables

By: Atif Shehzad | Updated: 2009-04-06 | Comments (5) | Related: > 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.

About the author

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

Article Last Updated: 2009-04-06

Comments For This Article

Monday, July 2, 2012 - 2:35:46 AM - Atif

@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

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

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

Thursday, March 1, 2012 - 4:28:09 AM - Atif shehzad

@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

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

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?

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.