Tips ‘N’ Tricks – SSMS – Script your actions in SSMS to Easily Generate T-SQL Scripts

Writing T-SQL Queries is fun! I really enjoy writing the T-SQL Queries and I prefer to do most of the things using T-SQL as much as possible. However, we, as humans, sometimes tend to forget the syntax for performing a particular type of task or even people who are new to SQL Server might find it difficult to remember the syntax of T-SQL Query to perform a particular task. Hence, sometimes we find ourselves going to SQL Server Books Online and other similar resources just to get the syntax even though we know what T-SQL constructs/features need to be used for performing a particular type of task.

To address this very concern, there is a really cool and handy feature available in SQL Server Management Studio (SSMS), which allows you to perform the intended task using a user friends Graphical User Interface (GUI) and later generate a T-SQL Script of all the actions you have performed. Let’s see how this feature works.

Let us say that you want to create a Database for which you do not know the exact syntax and the various options associated with the CREATE DATABASE syntax. You can generate the script for this scenario by following the below mentioned steps.

  1. Open SSMS and Connect to your SQL Server Instance.
  2. In the Object Explorer, Right click on Databases and select New Database… from context menu.
  3. Enter the database name as SSMSScriptingActionsDemoDB under General Page of New Database window.
  4. Go to Options Page and set the following options as shown in the below screenshot:
  1. Recovery model to Simple
  2. Auto Shrink to True
  3. Restrict Access to SINGLE_USER
  • Click on down arrow next to Script Option (at the top of the window) and select Script Action to New Query Window (or alternatively you can press Ctrl + Shift + N) as shown in the below screenshot.

Demo of Scripting Actions in SQL Server Management Studio (SSMS)

Demo of Scripting Actions in SQL Server Management Studio (SSMS)

The Script Generated by the above actions looks as shown below (in the below script, some parts have been removed due to space constraints and only the required part has been shown).

T-SQL Script Generated using the Scripting Actions Feature in SSMS

The numbers shown marked in the above Output T-SQL Script correspond to the Numbers Marked against corresponding settings in the second screenshot above.

That’s it! Generating a Script for your Intended Actions is as simple as that. What approach do you follow to perform similar operation/to achieve similar functionality?

Note: Above demonstration is based on SQL Server 2008 R2.

Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on June 13, 2012, in Development Tips, Shortcuts, SQL Client Tools, SQL Server, SSMS, SSMS Tips, T-SQL, T-SQL Tips, Tips 'N' Tricks and tagged , , , , , , , . Bookmark the permalink. 1 Comment.

What are your thoughts?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: