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.
Open SSMS and Connect to your SQL Server Instance.
In the Object Explorer, Right click on Databases and select New Database… from context menu.
Enter the database name as SSMSScriptingActionsDemoDB under General Page of New Database window.
Go to Options Page and set the following options as shown in the below screenshot:
Recovery model to Simple
Auto Shrink to True
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.
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).
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.
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 Databases, MSBI, SQL Server, SQL Server Management Studio, SQL Syntax, SSMS, T-SQL, Tips and Tricks. Bookmark the permalink. 1 Comment.