Scripting Objects in SSMS

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

 

Single Object

To script a specific SQL object: Right click on the object -> Script [Object Type] As [CREATE To: ALTER To: DROP To: DROP And CREATE To: SELECT To: INSERT To: UPDATE To: DELETE To:] -> Location [New Query Editor Window – File – Clipboard – Agent Job]

SingleObject

 

Object Explorer

Users are able to script an entire database from the Object Explorer.  This option scripts out only the database configuration options and not specific objects within the database.

Right click the selected Database -> Script Database As -> [CREATE To: DROP To: DROP And CREATE To:]  -> [New Query Editor Window File Clipboard Agent Job]

ObjectExplorer

 

Generate Scripts Wizard

This option provides the most options for selecting what is scripted and what is included.

Right click database -> Tasks -> Generate Scripts

GeerateScriptsWizard

 

This will open the Generate and Publish Scripts wizard.  You can select to script the entire database or specific object(s) within the database (Tables, Views, Stored Procedures, User-Defined Functions, User-defined Data Types, DDL Triggers, Xml Schema Collections, Schemas, Full Text Catalog).

ChooseObjects

 

Once you have selected everything to be scripted, click Next.  This will take you to the Set Scripting Options page.  Select the Output type and where the script will be saved.

SetOptions

 

Click on the Advanced button to modify any of the advanced scripting options.  When you are done making changes to the scripting options, click on the Close button.

advancedscriptingoptions

 

Click Next to review your scripting selections.

Summary

 

Click Next to save/publish scripts.

SaveScripts

 

Click Finish to close the Wizard.

SSMS Keyboard Shortcuts – Menu Activation

short·cut: An accelerated way of doing or achieving something

Within computing, there are so many shortcuts keys that make working in a specific app or program easier.  We even talk in shortcuts…BRB, LOL, TTYL.  There are even a lot of keyboard shortcuts in SQL Server Management Studio (SSMS) that can make your job easier.

 

CTRL+N

Open a new query window

 

ALT

Move to the SQL Server Management Studio menu bar

ALT
Once on the menu bar, hit F (File), E (Edit), V (View), Q (Query), P (Project), D (Debug), T (Tools) or H (Help) to access the appropriate menu.

ALTPLUS

 

ALT+HYPHEN

Activate the menu for a tool component

ALTHyphen

 

SHIFT+F10

Display the context menu

SHIFTF10

 

CTRL+O

Display the Open File dialog box to open an existing file

CTRLO

 

CTRL+SHIFT+Q

Display the Query Designer: This is a quick and dirty way to start creating a new query.

CTRLSHIFTQ

 

CTRL+SHIFT+N

Display the New Project dialog box to create a new project

CTRLSHIFTN

 

CTRL+SHIFT+O

Display the Open Project dialog box to open an existing project

CTRLSHIFTO

 

CTRL+SHIFT+A

Display the Add New Item dialog box to add a new file to the current project

CTRLSHIFTA

 

SHIFT+ALT+A

Display the Add Existing Item dialog box to add an existing file to the current project

SHIFTALTA

 

ESC

Close a menu or dialog box, canceling the action

Undock Tabs and Windows in SSMS to Create More Workspace

With all of the tabs and windows open in SSMS, you can start running out of space to work.  Most people have multiple screens now so you can take advantage of your set-up.  Starting with SQL Server 2012, users are able to undock the tabs and windows in SSMS and move them to their other monitor, thus creating more space in the query window to work.  The more monitors you have, the more you can spread out.  The tabs and windows that can be undocked and moved outside of your SSMS instance include:

  • Bookmark Window
  • Error List
  • Object Explorer
  • Object Explorer Details Tab
  • Output
  • Properties Tab
  • Registered Servers Tab
  • Solution Explorer
  • Task List
  • Template Browser
  • Toolbox
  • Web Browser

 

Below is the ‘normal’ setup we are used to with SSMS.  Everything is tucked in nicely in our application.

OneScreen

 

When you undock the tabs and windows, you are able to create more workspace within your open query window as well as increase the size of the other windows you are using.

TwoScreens

Don’t Really Copy That Blank Line I Selected

Turning Off ‘Apply Cut of Copy commands to blank lines when there is no selection’

Copy – Paste – Copy – Paste.  Most of us can do this in our sleep, we have copied and pasted so much text over the years.  With that said, we have all been in the middle of copying text, we get on a roll and sometimes hit the wrong keys.  In SQL Server Management Studio (SSMS), and most Microsoft products, Copy is CTRL+C and Paste is CTRL+V.  The C and the V are next to each other on the keyboard and I have fat fingered a paste and hit CTRL+C instead.  Ugh…go back to the original text and start again.

There is a setting that Microsoft has turned on by default in SSMS that could help when you have fat fingers.  It is the ‘Apply Cut or Copy commands to blank lines when there is no selection.’  With the setting turned off, when you have copied something and want to paste it but you hit CTRL+C a second time in the blank space, it does not get rid of what you have copied.  Hit CTRL+V and your text will be copied.  To change the setting, go to the Text Editor settings and disable the setting.

Tools -> Options -> Text Editor -> Transact-SQL -> General

Apply_SQL

 

There are different types of editors in SSMS so make sure the setting is changed for both Transact-SQL and Plain Text if you use both.  The text editor is used to edit documents not associated with any particular language.  For instance, if you open a .txt file in SSMS, you need to make sure that the setting is disabled for the Plain Text editor for your fat fingers to be saved.

Tools -> Options -> Text Editor -> Plain Text -> General

Apply_TXT

The box where you change your settings look very similar, so if you make the changes and it doesn’t work, double check which setting that you changed.  You want to make sure you have disabled the option for the right type of editor.  You might want to disable it in both places.

If you have not or do not want to disable this setting, use the Clipboard Cycle Ring feature to find the text that you copied.

Maneuvering Through Query Windows

Working in SSMS, you are likely to have multiple SQL Server query windows open and you need to maneuver through the different tabs.  You can click on the query window that you want to open if there are just a few tabs open .  Depending on the size of your screen, you might see only 4 or 5 open query windows and it always feels like the one you want is not in that top group.  There are a few ways to maneuver through your query windows, and everyone has their favorite.  This is usually based on the way you learned to maneuver through the query windows and/or the type of work that you do.  Some people love to use the mouse, other don’t take their hands off the keyboard.  There is more than one way to skin a cat and more than one way to maneuver through your open query query windows .

SkinACat

 

CTRL + F6

When you are in SSMS, pressing CTRL + F6 will cycle through the open query windows.  Continue to press CTRL + F6 until you get to the query window that you need.  Pressing CTRL + SHIFT + F6 will cycle through the open query windows in the reverse order.

 

CTRL + TAB

Use CTRL + TAB to switch between open query window tabs.  When you first hit CTRL + TAB, it will open a temporary window listing all of the open queries.  While continuing to hold down the CTRL button, click on the TAB button to get to the desired query window.  Using SHIFT + CTRL + TAB (holding the SHIFT + CTRL and clicking on the TAB button) will take you through the list in reverse order.

CTRLTAB

 

Dropdown Box

Another way to get around in your open query windows is to click in the dropdown box to the right of the open query windows with your mouse to produce a list of all of the open files.  Scroll within the list and select a specific query window you need.

DropdownBox

 

ALT + W

Press ALT + W to open the Windows tab from the menu bar.  At the bottom is a list of open query windows.  Use the up and down arrows to maneuver through the list and select the query window that you want to view.

ALTW

 

Through the Windows menu, it is easy to clean up your open query windows.  Hit ALT + W to open the Windows menu then select W or scroll down to Windows….  This will open a window all of your open query windows.  You can select just one open query window or many using the CTRL key.  With one, you can Activate, Save, or Close the query.  If you select more than one, you close those specific query windows.

ALTW-W

CTRL + ALT + L

Pressing CTRL + ALT + L will open the Solution Explorer on the right side of SSMS.  Once in the Solution Explorer, begin typing the query name and the cursor will go to that query.  This is not as handy if you have not saved or renamed the queries and they are still listed as SQLQuery1.sql, SQLQuery2.sql, SQLQuery3.sql, etc.  You can also use the up and down arrows to maneuver through the Solution Explorer to find the query window that you need to access.

CTRLALTL

 

Cycle Clipboard Ring

Standard in most Microsoft products are the commands CTRL+C to copy data and CTRL+V to paste data.  In SSMS, there is a tool called Cycle Clipboard Ring that allows the user to cycle through the last 20 items that have been saved to the clipboard.  Once you have gone through all of the items, you will start again through the items, thus the name ring.

In SSMS, open a new query window and use CTRL+SHIFT+V to paste the Cycle Clipboard Ring’s current item.  You also can access Cycle Clipboard Ring from the Edit dropdown menu.  However, Cycle Clipboard Ring may not appear in the Edit dropdown menu until it has been used once.

CycleClipboardRing

 

Repeatedly press CTRL+SHIFT+V to cycle through each entry within the Clipboard Ring until you find the item you want to use.  Each time you press CTRL+SHIFT+V, the current item replaces the last entry pasted.

Microsoft Explorer in SSMS

SQL Server Management Studio (SSMS) contains several tools that many people are not aware are available and can make you more productive in your job.  For instance, did you know that SSMS has a version of Microsoft Explorer that will open in a query tab?

While working in SSMS, there may be a need to refer to or browse the internet to answer questions about something you are working on, locate syntax or existing queries that will assist in resolving an issue you have or maybe view MSDN Library help topics.  Rather than leaving SSMS and opening a web browser, users can open a web browser page in SSMS.  It will open to the default Microsoft SQL Server website.  There are a few options you can use to open a web browser from inside SSMS:

  • From the keyboard: CTRL+ALT+R
  • From the Web Browser icon on the toolbar

WebBrowser

 

  • From the View Menu

WebBrowser2

 
So, what are some of the uses for a web browser in SSMS that can help you be more efficient?  One handy thing you can do is embed links in your code that you may need to reference.  You can hover over the link, select CTRL, and click on the link to open up the site.

CTRLClick

 

This is a great tool if you are a new DBA or are working on something that you do not have a lot of experience with.  One simple example, when creating a new table using T-SQL, have the data type web page open next to your code to make it easier to reference the various data types.  This will make you much more efficient that going back and forth between SSMS and your web browser outside of SSMS.

VerticalExample

 

There are many other ways you can use this tool.  Sometimes you might have something that you need to search the internet for information.  Rather than switching back and forth between SSMS and your external web browser, it is all right there for you to access.

 

Results to Grid

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect.  These changes can be made at:

Tools > Options > Query Results > Results to Grid

ResultsToGrid

 

Include Query in the Result Set

The default for Include Query in the Result Set is off.  When toggled on, the query is included in the messages tab of the results.

QueryInResult

Include Column Headers When Copying or Saving the Results

The default for Include Column Headers When Copying or Saving the Results is off.  When it is toggled on, the column headers are copied or saved with the output.  To copy the results, you would highlight the upper left corner of the result set and right click.  You can select Copy or Copy with Headers.  (You can also use CTRL+C (copy) or CTRL+SHIFT+C (copy with headers).)  Either way, the column headers will be copied along with the data.

When you save the query results (Save Results As…), there is not an option to include headers from the menu.  To include columns headers when saving data, this feature must be toggled on and it must be turned on prior to running the query for the Column Headers to be included in the output.

ColumnWhileCopying

 

Retain CR/LF on Copy or Save

When copying information into Notepad, the carriage return and line feeds from the text are not always carried over.  In the example below, I will show the difference in the results when this option is toggled on and off.

Note the two queries.  They are slightly different to make it easier to identify the results.  The query on the left has the Retain CR/LF on Copy or Save toggled on, the one on the right has the feature toggled off.  Looking at the results of each query, they are the same in the Results tab.  The difference occurs when you copy the data into another format, such as Notepad.

CR_LF

Discard Results After Execution

Really…discard the results after execution of a query.  Isn’t the point of running the query to get the results of the query?  Not always.  Suppose you need the results of the execution plan and not necessarily that of the data.  That would be one situation where you would not care about the result set.  By toggling on this option, you will save on memory but not CPU or IO.  If you are working with a large data set, this could also save you time as you do not have to wait for the dataset to load before getting the execution plan.  This is primarily a tool to use for troubleshooting.

DiscardResults

Display Results in a Separate Tab

The default setting in Query Editor is with Editor pane at the top and the Results and Messages pane at the bottom of the window.  Users can also set up the Results and Messages panes to appear at the top of the query window, allowing more space for results.  With the results in a separate tab, the tabs are on the same horizontal plane and it is easier to switch from the Editor to the Results to the Messages because they are all together.  To make this change, go to the following and toggle on ‘Display Results in a separate tab’.

The images below show the default Results pane at the bottom of the Query Window (Left) and with the results in a separate tab (Right).

SeparateTab

 

There is a setting under the Display Results in which you can toggle on: ‘Switch to the results tab after the query executes’.  With the display panes in separate, horizontal tabs, you can’t see the results after running a query like in the default format without switching to the Results pane.  This feature will save you that step of running the query then tabbing to the Results pane.  The Results tab will automatically open when executing the query.  The two options really go hand-in-hand.

 

AutoRecover in SSMS

 

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  The AutoRecover default settings in SSMS are:

Save AutoRecover information every 5 minutes

Keep AutoRecover information for 7 days

With the default settings, you can lose up to 5 minutes of work on a sudden reboot.  The recovered information will be kept for 7 days so there is some time to get your files.  These settings can be modified, changing how often auto recover happens and how long the information is retained.  It depends on the criticality of things that you work on as to how often you would want AutoRecover to save your scripts.  This feature can be turned off but it is not recommended.  To change the settings, go to:

Tools -> Options -> Environment -> AutoRecover

AutoRecover

 

When opening SSMS after that unexpected shutdown, SSMS tries to recover the unsaved queries and prompts a dialog box to the user.  You have the choice to recover files at that time.  If you skip over that step or the dialog box does not appear on re-open, the AutoRecovered files can be found on the server.  Navigate to the folder where the backup files are located and recover files
from there.

AutorecoverLocation

 

The settings in SSMS will determine what is available to recover and when it was last saved.  You don’t want AutoRecover to constantly run, but it needs to be set to a time that is appropriate for you and your job.

SQL Server Management Studio Query Shortcuts

Back in the day, with the introduction of programs like Word and Excel, I used keyboard shortcuts to make my job easier.  Then we started using a mouse and reduced the number of keyboard shortcuts I used.  It took me a long time to switch from the keyboard shortcuts to the mouse.  Now I am back to using shortcuts, especially in SQL Server Management Studio (SSMS).  Microsoft allows users to create shortcuts that, if you use them, could make your job easier.  Setting up the shortcuts in SSMS are pretty simple.

 

Tools > Options > Environment>Keyboard>Query Shortcuts

queryshortcuts

 

The three default shortcuts available to everyone are listed below.  These shortcuts are locked and cannot be changed by the user.

ALT+F1   sp_help Returns information about a database object or data type
CTRL+1   sp_who Returns information about current users, sessions and processes.
CTRL+2   sp_lock Returns information about locks

Other shortcuts can be set up under the available keystrokes.  You can create a shortcut for queries or stored procedures that you run often.  The example below is a query that I run often to see what jobs are currently running.  Note that to add it as a shortcut, the code needs to be on one line.

I added this query as CTRL+4 in SSMS.  Hit CTRL+4 (or the shortcut keystroke you designate) to run the job.  That is a lot easier than looking up the code, copying it or writing it out again, and running it every time you need to run the query.

querytsql

 

queryctrl4

 

If you want to select all of the records from a table, create a shortcut keystroke using SELECT * FROM as the stored procedure.  Enter the name of the table in the Query Editor, highlight the table name, and click CTRL+3 (or whatever shortcut keystroke you designate).  The results will appear in the results tab.

queryctrl3