100 keyboard shortcuts

100 keyboard shortcuts
*************** *************** ********************

* CTRL+C (Copy)
* CTRL+X (Cut)
* CTRL+V (Paste)
* CTRL+Z (Undo)
* DELETE (Delete)
* SHIFT+DELETE (Delete the selected item
permanently without placing the item in the
Recycle Bin)
* CTRL while dragging an item (Copy the
selected item)
* CTRL+SHIFT while dragging an item (Create a
shortcut to the selected item)
* F2 key (Rename the selected item)
* CTRL+RIGHT ARROW (Move the insertion
point to the beginning of the next word)
* CTRL+LEFT ARROW (Move the insertion point
to the beginning of the previous word)
* CTRL+DOWN ARROW (Move the insertion
point to the beginning of the next paragraph)
* CTRL+UP ARROW (Move the insertion point
to the beginning of the previous paragraph)
* CTRL+SHIFT with any of the arrow keys
(Highlight a block of text)
* SHIFT with any of the arrow keys (Select
more than one item in a window or on the
desktop, or select text in a document)
* CTRL+A (Select all)
* F3 key (Search for a file or a folder)
* ALT+ENTER (View the properties for the
selected item)
* ALT+F4 (Close the active item, or quit the
active program)
* ALT+ENTER (Display the properties of the
selected object)
* ALT+SPACEBAR (Open the shortcut menu for
the active window)
* CTRL+F4 (Close the active document in
programs that enable you to have multiple
documents open simultaneously)
* ALT+TAB (Switch between the open items)
* ALT+ESC (Cycle through items in the order
that they had been opened)
* F6 key (Cycle through the screen elements in
a window or on the desktop)
* F4 key (Display the Address bar list in My
Computer or Windows Explorer)
* SHIFT+F10 (Display the shortcut menu for
the selected item)
* ALT+SPACEBAR (Display the System menu for
the active window)
* CTRL+ESC (Display the Start menu)
* ALT+Underlined letter in a menu name
(Display the corresponding menu)
* Underlined letter in a command name on an
open menu (Perform the corresponding
command)
* F10 key (Activate the menu bar in the active
program)
* RIGHT ARROW (Open the next menu to the
right, or open a submenu)
* LEFT ARROW (Open the next menu to the
left, or close a submenu)
* F5 key (Update the active window)
* BACKSPACE (View the folder one level up in
My Computer or Windows Explorer)
* ESC (Cancel the current task)
* SHIFT when you insert a CD-ROM into the CD-
ROM drive (Prevent the CD-ROM from
automatically playing)
Dialog Box Keyboard Shortcuts
--------------- --------------- --------------- ----------
* CTRL+TAB (Move forward through the tabs)
* CTRL+SHIFT+TAB (Move backward through
the tabs)
* TAB (Move forward through the options)
* SHIFT+TAB (Move backward through the
options)
* ALT+Underlined letter (Perform the
corresponding command or select the
corresponding option)
* ENTER (Perform the command for the active
option or button)
* SPACEBAR (Select or clear the check box if
the active option is a check box)
* Arrow keys (Select a button if the active
option is a group of option buttons)
* F1 key (Display Help)
* F4 key (Display the items in the active list)
* BACKSPACE (Open a folder one level up if a
folder is selected in the Save As or Open
dialog box)
Microsoft Natural Keyboard Shortcuts
--------------- --------------- --------------- ----------
* Windows Logo (Display or hide the Start
menu)
* Windows Logo+BREAK (Display the System
Properties dialog box)
* Windows Logo+D (Display the desktop)
* Windows Logo+M (Minimize all of the
windows)
* Windows Logo+SHIFT+M (Restore the
minimized windows)
* Windows Logo+E (Open My Computer)
* Windows Logo+F (Search for a file or a
folder)
* CTRL+Windows Logo+F (Search for
computers)
* Windows Logo+F1 (Display Windows Help)
* Windows Logo+ L (Lock the keyboard)
* Windows Logo+R (Open the Run dialog
box)
* Windows Logo+U (Open Utility Manager)
Accessibility Keyboard Shortcuts
--------------- --------------- --------------- ----------
* Right SHIFT for eight seconds (Switch
FilterKeys either on or off)
* Left ALT+left SHIFT+PRINT SCREEN (Switch
High Contrast either on or off)
* Left ALT+left SHIFT+NUM LOCK (Switch the
MouseKeys either on or off)
* SHIFT five times (Switch the StickyKeys either
on or off)
* NUM LOCK for five seconds (Switch the
ToggleKeys either on or off)
* Windows Logo +U (Open Utility Manager)
Windows Explorer Keyboard Shortcuts
--------------- --------------- --------------- ----------
* END (Display the bottom of the active
window)
* HOME (Display the top of the active window)
* NUM LOCK+Asterisk sign (*) (Display all of
the subfolders that are under the selected
folder)
* NUM LOCK+Plus sign (+) (Display the
contents of the selected folder)
* NUM LOCK+Minus sign (-) (Collapse the
selected folder)
* LEFT ARROW (Collapse the current selection
if it is expanded, or select the parent folder)
* RIGHT ARROW (Display the current selection
if it is collapsed, or select the first subfolder)
Shortcut Keys for Character Map
--------------- --------------- --------------- ----------
* After you double-click a character on the
grid of characters, you can move through the
grid by using the keyboard shortcuts:
* RIGHT ARROW (Move to the right or to the
beginning of the next line)
* LEFT ARROW (Move to the left or to the end
of the previous line)
* UP ARROW (Move up one row)
* DOWN ARROW (Move down one row)
* PAGE UP (Move up one screen at a time)
* PAGE DOWN (Move down one screen at a
time)
* HOME (Move to the beginning of the line)
* END (Move to the end of the line)
* CTRL+HOME (Move to the first character)
* CTRL+END (Move to the last character)
* SPACEBAR (Switch between Enlarged and
Nor mal mode when a character is selected)
Microsoft Management Console (MMC) Main
Window Keyboard Shortcuts
--------------- --------------- --------------- ----------
* CTRL+O (Open a saved console)
* CTRL+N (Open a new console)
* CTRL+S (Save the open console)
* CTRL+M (Add or remove a console item)
* CTRL+W (Open a new window)
* F5 key (Update the content of all console
windows)
* ALT+SPACEBAR (Display the MMC window
menu)
* ALT+F4 (Close the console)
* ALT+A (Display the Action menu)
* ALT+V (Display the View menu)
* ALT+F (Display the File menu)
* ALT+O (Display the Favorites menu)
MMC Console Window Keyboard Shortcuts
--------------- --------------- --------------- ----------
* CTRL+P (Print the current page or active
pane)
* ALT+Minus sign (-) (Display the window
menu for the active console window)
* SHIFT+F10 (Display the Action shortcut
menu for the selected item)
* F1 key (Open the Help topic, if any, for the
selected item)
* F5 key (Update the content of all console
windows)
* CTRL+F10 (Maximize the active console
window)
* CTRL+F5 (Restore the active console
window)
* ALT+ENTER (Display the Properties dialog
box, if any, for the selected item)
* F2 key (Rename the selected item)
* CTRL+F4 (Close the active console window.
When a console has only one console
window, this shortcut closes the console)
Remote Desktop Connection Navigation
--------------- --------------- --------------- ----------
* CTRL+ALT+END (Open the m*cro$oft
Windows NT Security dialog box)
* ALT+PAGE UP (Switch between programs
from left to right)
* ALT+PAGE DOWN (Switch between programs
from right to left)
* ALT+INSERT (Cycle through the programs in
most recently used order)
* ALT+HOME (Display the Start menu)
* CTRL+ALT+BREAK (Switch the client
computer between a window and a full
screen)
* ALT+DELETE (Display the Windows menu)
* CTRL+ALT+Minus sign (-) (Place a snapshot
of the active window in the client on the
Terminal server clipboard and provide the
same functionality as pressing PRINT SCREEN
on a local computer.)
* CTRL+ALT+Plus sign (+) (Place a snapshot of
the entire client window area on the Terminal
server clipboard and provide the same
functionality as pressing ALT+PRINT SCREEN
on a local computer.)
Internet Explorer navigation
--------------- --------------- --------------- ----------
* CTRL+B (Open the Organize Favorites dialog
box)
* CTRL+E (Open the Search bar)
* CTRL+F (Start the Find utility)
* CTRL+H (Open the History bar)
* CTRL+I (Open the Favorites bar)
* CTRL+L (Open the Open dialog box)
* CTRL+N (Start another instance of the
browser with the same Web address)
* CTRL+O (Open the Open dialog box, the
same as CTRL+L)
* CTRL+P (Open the Print dialog box)
* CTRL+R (Update the current Web page)
* CTRL+W (Close the current window)

Getting started with SQL Server Management Objects (SMO)

 

ProblemSQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000. In this tip, I am going to discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language.

SolutionAlthough SQL Server Management Studio (SSMS) is a great tool to manage a SQL Server instance there might be a need to manage your SQL Server instance programmatically.
For example, consider you are developing a build deployment tool, this tool will deploy the build but before that it needs to make sure that the SQL Server and SQL Server Agent services are running, a database is available and online. For this kind of work, you can use SMO, a SQL Server API object model.
The SMO object model represents SQL Server as a hierarchy of objects. On top of this hierarchy is the Server object, beneath it resides all the instance classes.
SMO classes can be categorized into two categories:

• Instance classes - SQL Server objects are represented by instance classes. It forms a hierarchy that resembles the database server object hierarchy. On top of this hierarchy is Server and under this there is a hierarchy of instance objects that include: databases, tables, columns, triggers, indexes, user-defined functions, stored procedures etc. I am going to demonstrate the usage of a few instance classes in this tip in the example section below.
• Utility classes - Utility classes are independent of the SQL Server instance and perform specific tasks. These classes have been grouped on the basis of its functionalities. For example Database scripting operations, Backup and restore databases, Transfer schema and data to another database etc. I will discussing the utility classes in my next tip.

How SMO is different from SQL-DMO
SMO object model is based on managed code and implemented as .NET Framework assemblies. It provides several benefits over traditional SQL-DMO along with support for new features introduced with SQL Server 2005 and SQL Server 2008.
For example

• It offers improved performance by loading an object only when it is referenced, even the objects properties are loaded partially on object creation and left over objects are loaded only when they are directly referenced.
• It groups the T-SQL statements into batches to improve network performance.
• It now supports several new features like table and index partitioning, Service Broker, DDL triggers, Snapshot Isolation and row versioning, Policy-based management etc.

An exhaustive list of the comparisons between SQL-DMO and SMO can be found here.
Example
Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. To add a reference of these assemblies, go to Solution Browser - > References -> Add Reference. Add these commonly used assemblies. 

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoEnum.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll // on SQL Server/VS 2008 only

 

There are a couple of other assemblies which contain namespaces for certain tasks, but few of them are essential to work with SMO. Some of the frequently used namespaces and their purposes are summarized in the below table, other namespaces are used for specific tasks like working with SQL Server Agent where you would reference Microsoft.SqlServer.Management.Smo.Agent etc.

Namespaces	Purpose
Microsoft.SqlServer.Management.Common	It contains the classes which you will require to make a connection to a SQL Server instance and execute Transact-SQL statements directly.
Microsoft.SqlServer.Management.Smo	This is the basic namespace which you will need in all SMO applications, it provides classes for core SMO functionalities. It contains utility classes, instance classes, enumerations, event-handler types, and different exception types.
Microsoft.SqlServer.Management.Smo.Agent	It provides the classes to manage the SQL Server Agent, for example to manage Job, Alerts etc.
Microsoft.SqlServer.Management.Smo.Broker	It provides classes to manage Service Broker components using SMO.
Microsoft.SqlServer.Management.Smo.Wmi	It provides classes that represent the SQL Server Windows Management Instrumentation (WMI). With these classes you can start, stop and pause the services of SQL Server, change the protocols and network libraries etc.

C# Code Block 1Here I am using the Server instance object to connect to a SQL Server. You can specify authentication mode by setting the LoginSecure property. If you set it to "true", windows authentication will be used or if you set it to "false" SQL Server authentication will be used.

With Login and Password properties you can specify the SQL Server login name and password to be used when connecting to a SQL Server instance when using SQL Server authentication.

C# Code Block 1 - Connecting to server

Server myServer = new Server(@"ARSHADALI\SQL2008"); //Using windows authenticationmyServer.ConnectionContext.LoginSecure = true; myServer.ConnectionContext.Connect(); //// //Do your work //// if (myServer.ConnectionContext.IsOpen) myServer.ConnectionContext.Disconnect(); //Using SQL Server authenticationmyServer.ConnectionContext.LoginSecure = false; myServer.ConnectionContext.Login = "SQLLogin"; myServer.ConnectionContext.Password = "entry@2008";

C# Code Block 2Once a connection has been established to the server, I am enumerating through the database collection to list all the database on the connected server. Then I am using another instance class Database which represents the AdventureWorks database. Next I am enumerating through the table, stored procedure and user-defined function collections of this database instance to list all these objects. Finally I am using the Table instance class which represents the Employee table in the AdventureWorks database to enumerate and list all properties and corresponding values.

C# Code Block 2 - retrieving databases, tables, SPs, UDFs and Properties

//List down all the databases on the server foreach (Database myDatabase in myServer.Databases) { Console.WriteLine(myDatabase.Name); } Database myAdventureWorks = myServer.Databases["AdventureWorks"]; //List down all the tables of AdventureWorks foreach (Table myTable in myAdventureWorks.Tables) { Console.WriteLine(myTable.Name); } //List down all the stored procedures of AdventureWorksforeach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures) { Console.WriteLine(myStoredProcedure.Name); } //List down all the user-defined function of AdventureWorks foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions) { Console.WriteLine(myUserDefinedFunction.Name); } //List down all the properties and its values of [HumanResources].[Employee] tableforeach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee", "HumanResources"].Properties) { Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value); }

C# Code Block 3This demonstrates the usage of SMO to perform DDL operations.
First I am checking the existence of a database, if it exists dropping it and then creating it.
Next I am creating a Table instance object, then creating Column instance objects and adding it to the created Table object. With each Column object I am setting some property values.
Finally I am creating an Index instance object to create a primary key on the table and at the end I am calling the create method on the Table object to create the table.

C# Code Block 3 - Creating a database and table

//Drop the database if it existsif(myServer.Databases["MyNewDatabase"] != null) myServer.Databases["MyNewDatabase"].Drop(); //Create database called, "MyNewDatabase"Database myDatabase = new Database(myServer, "MyNewDatabase"); myDatabase.Create(); //Create a table instanceTable myEmpTable = new Table(myDatabase, "MyEmpTable"); //Add [EmpID] column to created table instanceColumn empID = new Column(myEmpTable, "EmpID", DataType.Int); empID.Identity = true; myEmpTable.Columns.Add(empID); //Add another column [EmpName] to created table instanceColumn empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200)); empName.Nullable = true; myEmpTable.Columns.Add(empName); //Add third column [DOJ] to created table instance with default constraintColumn DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime); DOJ.AddDefaultConstraint(); // you can specify constraint name here as well DOJ.DefaultConstraint.Text = "GETDATE()"; myEmpTable.Columns.Add(DOJ); // Add primary key index to the tableIndex primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable"); primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey; primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID")); myEmpTable.Indexes.Add(primaryKeyIndex); //Unless you call create method, table will not created on the server myEmpTable.Create(); Result:

The complete code listing (created using SQL Server 2008 and Visual Studio 2008, although there is not much difference if you are using SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

 

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
namespace LearnSMO2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Server myServer = new Server(@"ARSHADALI\SQL2008");
            try
            {
                //Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                //Using SQL Server authentication
                //myServer.ConnectionContext.LoginSecure = false;
                //myServer.ConnectionContext.Login = "SQLLogin";
                //myServer.ConnectionContext.Password = "entry@2008";
                myServer.ConnectionContext.Connect();
                //AccessingSQLServer(myServer);
                DatabaseObjectCreation(myServer);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
                Console.ReadKey();
            }
           
        }
        private static void AccessingSQLServer(Server myServer)
        {
            //List down all the databases on the server
            foreach (Database myDatabase in myServer.Databases)
            {
                Console.WriteLine(myDatabase.Name);
            }
            Database myAdventureWorks = myServer.Databases["AdventureWorks"];
            //List down all the tables of AdventureWorks
            foreach (Table myTable in myAdventureWorks.Tables)
            {
                Console.WriteLine(myTable.Name);
            }
            //List down all the stored procedures of AdventureWorks
            foreach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures)
            {
                Console.WriteLine(myStoredProcedure.Name);
            }
            //List down all the user-defined function of AdventureWorks
            foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions)
            {
                Console.WriteLine(myUserDefinedFunction.Name);
            }
            //List down all the properties and its values of [HumanResources].[Employee] table
            foreach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee",
                "HumanResources"].Properties)
            {
                Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value);
            }
        }
        private static void DatabaseObjectCreation(Server myServer)
        {
            //Drop the database if it exists
            if(myServer.Databases["MyNewDatabase"] != null)
                myServer.Databases["MyNewDatabase"].Drop();
            //Create database called, "MyNewDatabase"
            Database myDatabase = new Database(myServer, "MyNewDatabase");
            myDatabase.Create();
            //Create a table instance
            Table myEmpTable = new Table(myDatabase, "MyEmpTable");
            //Add [EmpID] column to created table instance
            Column empID = new Column(myEmpTable, "EmpID", DataType.Int);
            empID.Identity = true;
            myEmpTable.Columns.Add(empID);
            //Add another column [EmpName] to created table instance
            Column empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200));
            empName.Nullable = true;
            myEmpTable.Columns.Add(empName);
            //Add third column [DOJ] to created table instance with default constraint
            Column DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime);
            DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
            DOJ.DefaultConstraint.Text = "GETDATE()";
            myEmpTable.Columns.Add(DOJ);
            // Add primary key index to the table
            Index primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable");
            primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID"));
            myEmpTable.Indexes.Add(primaryKeyIndex);
            //Unless you call create method, table will not created on the server           
            myEmpTable.Create();
        }

    }
}

Notes:

• If you have an application written in SQL-DMO and want to upgrade it to SMO, that is not possible, you will need to rewrite your applications using SMO classes.
• SMO assemblies are installed automatically when you install Client Tools.
• Location of assemblies in SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• SMO provides support for SQL Server 2000 (if you are using SQL Server 2005 SMO it supports SQL Server 7.0 as well) but a few namespaces and classes are not supported in prior versions.

Get script for every action in SQL Server Management Studio

 

Problem

I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to generate T-SQL scripts. However there are numerous database and server level tasks that I use the SSMS GUI and I would like to have a script of these changes for later reference. Examples of such actions through the SSMS GUI are backup/restore, changing compatibility level of a database, manipulating permissions, dealing with database or log files or creating/manipulating any login/user. I am looking for any way to generate T-SQL code for such actions, so that it may be kept for later reference. Also I would like to be able to reuse this T-SQL code for database tasks or scheduled jobs if needed.

Solution

SQL Server Management Studio (SSMS) provides a very good option to generate scripts for any operation performed through the GUI. It is an effective way to save the T-SQL code of actions performed through SSMS. Here is list of some of the tasks categories for which you may generate T-SQL scripts from SSMS GUI actions

• Changing any server instance level option
• Changing any database level option
• Managing server roles, logins, permissions
• Managing database roles, users, permissions
• Backup/Restore operations
• Managing policies (SQL Server 2008)

The above mentioned categories cover almost all operations that you may require to have script for. Now here are the simple steps to use this powerful option of SSMS.

• Open SSMS GUI for any required task
• Configure values in the GUI window
• Before clicking OK, find the Script option in the upper top of the GUI frame as shown below

• Click on the down arrow pointer and four options will be displayed to manipulate the action script

• Choose the appropriate option and click OK to complete the required task

The options shown are pretty self explanatory. You can directly open the script in a SSMS query window, directly save the script to a .SQL file or put the script in the Windows clipboard to paste where required. The last option is related to scheduled jobs and it will not be enabled in SSMS SQL Server Express edition.

---

Here are some scenarios to use this simple yet powerful option of SSMS. You may go through any of these examples to get familiar with the functionality.
Example 1: Enable filestream "Transact-SQL access enabled" option and open script for this action in a new SSMS query pane

• Right click on SQL Server 2008 instance in SSMS and select Properties
• Click on Advanced option in left panel
• Select 'Transact-SQL access enabled" for the Filestream Access Level option
• Before clicking OK to save the setting, click on arrow pointer next to the Script option

• Select first option "Script Action to New Query Window"
• The script is now in a new SSMS query pane and you can click the OK button to complete the action or Cancel to just have the script.

It is notable that instead of choosing the option from the drop list through small arrow, if you click directly on the Script option the script will be created in a new query Window, because this is the default option.

---

Example 2: Create a new database through SSMS and save the script for this action in .SQL file

• Right click on Databases folder
• Choose to "New Database.." from menu
• Enter name for new database and configure any other required options
• Before clicking OK to save the setting, click on arrow pointer provided with Script option

• Select second option "Script Action to File"
• Save the script file by providing a name in the file save dialogue and you may click OK button to create the database or Cancel to just have the script.

---

Example 3: Disable a Login through SSMS and copy the script for this operation to clipboard

• Right click on a login in Security folder in SSMS and select Properties
• Click on Status option in left pane
• Check the "Disabled" radio button
• Before clicking OK to save the action, click on arrow pointer provided with Script option

• Select third option "Script Action to Clipboard"
• Click OK button to disable the particular login or Cancel to just have the script.

Now you can paste the script as needed to confirm the operation.

---

Example 4: Create database backup through SSMS (non express edition) and directly create a schedule job for this action

• Right click on appropriate database in SSMS
• Go to Tasks and click on Back Up option
• Provide backup name and path along with other required customized options
• Before clicking OK to save the action, click on arrow pointer provided with Script option

• Select the fourth option "Script Action to Job"

A job configuration window will open with the create backup script already present in the job step. Note: this option will not be enabled in SSMS SQL Server Express edition.

---

Shortcuts
Instead of using the options through the menu in upper part of the GUI frame, we can also use shortcuts for any of the four options. These are noted below:

 

Hiding System Objects in Object Explorer in SQL Server Management Studio

 

Problem

While looking through the new features and improvements in SQL Server Management Studio (SSMS) we found a potentially interesting one to Hide System Objects in Object Explorer in SQL Server Management Studio. In this tip we will take a look at how to Hide System Objects in Object Explorer.

Solution

You may have noticed that once you are connected to a SQL Server Instance using SQL Server Management Studio, in the Databases node of Object Explorer you can see system objects such as the system databases as shown in the snippet below.

You can hide system objects in Object Explorer by following the below mentioned steps:
1. In SQL Server Management Studio, under Tools menu, click Options as shown in the snippet below.

2. In the Options dialog box, expand Environment and then select the General tab as shown in the snippet below. Select Hide system objects in Object Explorer and then click OK.

3. In the Microsoft SQL Server Management Studio dialog box, click OK to acknowledge that the changes will come into effect once you restart SQL Server Management Studio.

4. Next, go ahead and close SQL Server Management Studio. When you reopen SQL Server Management Studio once you are connected to SQL Server Instance you will not see the System Objects in Object Explorer in SQL Server Management Studio as shown in the snippet below.

 

SQL Server Management Studio customized startup options

 

ProblemSQL Server Management Studio (SSMS) is now the primary tool that we all use to manage SQL Server.  Whenever I open up SSMS I always go through the same steps to connect to a server and open certain query files.  Are there any shortcuts or alternative ways of starting SSMS?
Solution
SQLWB (sqlwb.exe) is the executable file that launches SQL Server Management Studio (SSMS). Most-likely the name corresponded to the original working name for Management Studio during the development phase of Yukon (the project title for what would eventually become SQL Server 2005): SQL Server Workbench. What many SQL Server professionals fail to realize is that the startup behavior of SSMS is customizable.  By simply passing parameter values along with the command to launch sqlwb, you can open default queries, projects, or connections.  You can also control whether to launch SSMS with the application's splash screen.  Let's take a look at the list of parameters available, courtesy of SQL Server Books Online:

sqlwb [scriptfile] [projectfile] [solutionfile] [-S servername] [-d databasename] [-U username] [-P password] [-E use Windows security] [-nosplash] [-?]

Arguments
The [scriptfile], [projectfile], and [solutionfile] parameters specify a file (or in the case of [scriptfile], the possibility of multiple files to open upon launch of SSMS.  If  you do not specify parameter values for servername, databasename, username, or password when you launch sqlwb and specify a script(s), project, or solution you will be prompted for the applicable security context for the file(s) you are opening.
Let's look at some examples of the behavior associated with the various options for launching SQL Server Management Studio from a Run command or Command Prompt:
Open a single script (.sql) file upon SSMS startup

sqlwb "C:\Temp\Config1.sql"

This command launches SQL Server Management Studio and prompts you for the connection information.  Note the query name in the background of this screen shot.

Open multiple sql query (.sql) files upon SSMS startup
The process for opening multiple SQL query files is only slightly different, simply list each of the full file paths for each query file you wish to open, separated by a space, after the call to sqlwb as shown in this screen image:

sqlwb "C:\Temp\Config1.sql" "C:\Temp\Config2.sql"

Without specifying the connection information in the run command for sqlwb you will be prompted for the SQL instance and security information upon launch of SSMS.  Note that once authenticated each of the queries connect using that same criteria.  This bears repeating: each .sql file will connect to the instance you specify, as the login you specify, when launching SSMS in this manner.

Open a SQL Server Management Studio Project (.ssmssqlproj) file upon SSMS startup
Microsoft provided an additional layer of project management with the release of SQL Server 2005.  The concept of a solution and project was nothing new to the developers out there.  This concept has been a component of the Visual Studio architecture for many previous releases.  However, in the continuing streamlining between SQL Server and Visual Studio interfaces, the concept finally was incorporated into the SQL Server management tools.  Simply put, a SQL Server Project file is a collection of various connections,  queries, and other objects that are organized to be utilized for a common purpose.  I personally use SSMS Projects for such purposes as Standard Installations, Daily Maintenance Checks, and Security Audits.  Just as SSMS Projects are a collection of multiple components, SSMS Solutions are a collection of multiple SSMS Projects.  The syntax for launching an SSMS Project or Solution is no different than launching a single .sql file from the command line or Run menu. 
Let's look at the following example; we will specify an existing .ssmssqlproj file, connecting with Integrated (Windows) security to the Sauron.Northwind database.

sqlwb "C:\Config\Config.ssmssproj" -S sauron -d Northwind -E

Additionally, you can open a SSMS Solution by providing a solution file path (...\*.ssmssln) as a parameter.  The following command opens the solution file Config.ssmssln, passing the connection information for the Foo login against the sauron instance of SQL Server:

sqlwb "C:\Temp\SSMS Projects\Configuration\Configuration.ssmssln"-S sauron -d Northwind -U Foo -P pwFoo

Additional Parameters
Here are some additional options.

• Launches SQL Server Management Studio without the splash screen

sqlwb -nosplash

• At any time you are unsure about the parameters available for sqlwb, you can append the -? parameter to display the following help information.

sqlwb -?

 Don't feel that you're forced into launching SQL Server Management Studio with the default splash screen and an initial instance connection.  The parameters associated with sqlwb.exe allow you to specifically control your startup parameters for files and connections.

 

SSMS keyboard shortcuts

 

Problem

As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.

Solution

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more. Let's jump right in.

---

SQL Server Management Studio Intellisense

SQL Server 2008 and later versions include IntelliSense which let's you know about objects in the database, supports T-SQL syntax, gives the parameter info of the stored procedures or functions. Although IntelliSense works by default (you can enable or disable it as and when required), there are times when you want to manually list a set of objects or members. This can be accomplished by pressing CTRL+J as shown below:

Stored procedures and functions normally accept parameters, when you want to execute a stored procedure it is good to know the name, data type and number of parameters to pass it for execution. To accomplish this, press CTRL+SHIFT+SPACE after the stored procedure name to view the list parameters as shown below:

To minimize the typing effort, you can use the auto complete feature of IntelliSense. Simply press ALT+RIGHT ARROW and it will complete your word by the best and first matching member list. If it finds more than one item it shows a list as shown below to choose one. You can also press CTRL+SPACE to bring up this list.

When we first connect to the database, list members are queried and stored in the local cache for better performance. Unfortunately, if someone creates additional objects, those new objects will not be listed. When this occurs, you need to refresh the local cache. Refreshing the local cache can be accomplished by pressing the CTRL+SHIFT+R keys or you can also perform this actions using the menu bar (Edit | IntelliSense | Refresh Local Cache) shown below:

---

Execute Scripts in SQL Server Management Studio

Now lets move on and see how to execute scripts using the shortcut keys. If you want to parse all the scripts without executing them in the current query window, simply press CTRL+F5 or you can select some lines of code to be parsed then press CTRL+F5. If you want to execute all the scripts in the current query window simply press F5 or you can select some lines of code to be executed then press F5. After the execution a result pane appears on the bottom section of SSMS. You can press CTRL+R to toggle the result pane as shown below. As you can notice in the image below it has a Results tab which shows the result sets returned after execution of the query and a Messages tab which shows all the messages generated/printed during execution, for example number of records affected etc.

By default the result set is displayed in the grid format, but you can change it to display as text or send the results directly to a file. To change the setting to display results as text, simply press CTRL+T before execution and result would display as shown below next time when you execute your script. To send the results to a file press CTRL+SHIFT+F before script execution. If you want to revert the setting back to return the results in a grid you can press CTRL+D before script execution and next time you will see your results in the grid.

---

Query Execution Plans in SQL Server Management Studio

During query analysis you might need to know the execution plan of a query even before executing it. To find this out, press CTRL+L to display the estimated query execution plan as shown below without actually running the whole query (in fact it runs for top 1 row).

On the other hand, you might need more detailed information and review the query execution plan with the query execution results. To capture this information, press CTRL+M to include the actual execution plan as shown below with the query results. You can even press SHIFT+ALT+S to include the client statistics with the query results.

If for some reason you would like to cancel a long running query, for that hit ALT+BREAK to cancel the current query execution.

---

SQL Server Management Studio Debugging

In SQL Server Management Studio, you can debug scripts. To start debugging from the first line press ALT+F5. To toggle a breakpoint on the line simply traverse to that line and press F9. To delete all the breakpoints from the query window at once, press CTRL+SHIFT+F9. A breakpoint is denoted by a tiny red circle placed on the left side of the query window as shown below:

To manage all the breakpoints in one place, SSMS has Breakpoint window as similar to the Bookmark window. To launch the Breakpoint window press CTRL+ALT+B and you will see one window as shown below:

While debugging you can press F11 to step into the called module or F10 to step over the called module. You can also press F5 to go to the next breakpoint instead of going one statement at a time.
Sometimes when you are working on a long script in the SSMS query window, it might appear small for you, in that case you can hit SHIFT+ALT+ENTER to toggle the query window in full screen mode as you can see below:

FFor help, you can select the object and press ALT+F1 to display the meta information about that object stored in the database as shown below.

If you are looking for help in Books Online you can select the keyword and press F1 to launch BOL with information about that selected keyword if it exists.

---

Accessing common windows in SQL Server Management Studio

Here are the shortcut keys for common SSMS windows:

• F8 - Object Explorer
• CTRL+ALT+T - Template Explorer
• CTRL+ALT+L - Solution Explorer
• F4 - Properties window
• CTRL+ALT+G - Registered Servers explorer

---

Standard shortcut keys

Please note apart from the above mentioned shortcut keys here are some standard shortcut keys which work fine in SSMS:

• CTRL+A - Select all the text in the current query window
• CTRL+C - Copy text in the current query window
• CTRL+V - Paste text in the current query window
• CTRL+X - Cut text in the current query window
• DEL - Delete text in the current query window
• CTRL+P - Launch the print dialog box
• CTRL+HOME - Top of the current query window
• CTRL+END - End of the query window
• CTRL+SHIFT+HOME - Select all the text from the current location to the beginning of the query window
• CTRL+SHIFT+END - Select all the text from the current location to the end of the query window
• ALT+F4 - Close the SSMS application

 

SQL Server Management Studio keyboard shortcuts

SQL Server Management Studio keyboard shortcuts 

 

Problem

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys.

Solution

We often overlook the shortcut keys which SSMS (SQL Server Management Studio) provides for increasing our productivity as a DBA or developer. In this tip series I will talk about some of these shortcut keys to help you use SSMS for more proficiently.

Launching SSMS

When launching SSMS you probably go through START-> All Programs -> SQL Server 20XX and click on SQL Server Management Studio, but this is not the only way.
Another options is to click on START -> Run or press Windows + R, type ssms and click OK (or hit ENTER) which will launch SSMS.
You can also specify different parameters or switches

• The -E switch will let you connect to the local instance using Windows authentication.
• The -U switch is used to specify a user and -P to specify the password
• If you want SSMS to connect to a specific database you can use the -d switch
• If you want a script file to be opened in SSMS you can specify the location and name of the file. This will just open the file in SSMS and will not execute the code. If you need to execute a script file you can use the SQLCMD utility.
• To close SSMS you can use ALT+F4.

As I said before, you can simply open the SSMS or you can specify the -E switch to open SSMS and connect using Windows authentication. If the current user does not have sufficient permissions obviously it will fail.

When we open SSMS a splash screen appears while loading SSMS in the memory. You can specify -nosplash switch which opens SSMS without the splash screen.

If you are scratching your head trying to remember all these switches, you don't need to because you can use -? which gives you the different command options as shown below. You can find more information about this here.

---

Changing Databases

Once you are in a Query Window in SSMS you can use CTRL+U to change the database. When you press this combination, the database combo-box will be selected as shown below. You can then use the UPDOWN arrow keys to change between databases (or type a character to jump to databases starting with that character) select your database and hit ENTER to return back to the Query Window. and

---

Changing Code Case (Upper or Lower)

When you are writing code you may not bother with using upper or lower case to make your code easier to read. To fix this later, you can select the specific text and hit CTRL+SHIFT+U to make it upper case or use CTRL+SHIFT+L to make it lower case as shown below.

---

Commenting Out Code

When writing code sometimes you need to comment out lines of code. You can select specific lines and hit CTRL+K followed by CTRL+C to comment it out and CTRL+K followed by CTRL+U to uncomment it out as shown below.

---

Indenting Code

As a coding best practice you should to indent your code for better readability. To increase the indent, select the lines of code (to be indented) and hit TAB as many times as you want to increase the indent likewise to decrease the indent again select those lines of code and hit SHIFT+TAB.

---

Bookmarking Code

When you have hundreds of lines of code, it becomes difficult to navigate. In this case you can bookmark lines to which you would like to return to. Hit CTRL+K followed by CTRL+K again to toggle the bookmark on the line. When you bookmark a line a tiny light blue colored square appears on the left side of the query window to indicate that the line has been bookmarked as you can see in the image below.
You can press CTRL+K followed by CTRL+N to move to the next bookmarked line from the current cursor location likewise to move back to the last bookmarked line you can hit CTRL+K followed CTRL+P from the current cursor location.
To clear all bookmarks from the current window you can hit CTRL+K followed by CTRL+L.

There might be several bookmarks you have placed in your query window and to manage these easily SSMS provides a Bookmarks window. To launch this window simply hit CTRL+K followed by CTRL+W and you can manage almost every aspect of bookmarking from this window as shown below including renaming the bookmarks.

---

Search and Find / Replace Text

Sometimes you need to find specific keywords or replace some specific keyword with another keyword. To launch the Quick Find dialog box press CTRL+F or CTRL+H for the Quick Replace dialog box. You can even fine tune your search with other options available in the dialog box or you can bookmark all the lines which contain your search string. To close the dialog box press ESC. You can also press F3 to find the next keyword match.

---

Goto Line Number

If you know the line number you want to go to you can use CTRL+G to open the Go To Line dialog box and type the line number and press OK (or hit ENTER) to go to that particular line number.

---

Opening Query Windows and Switching Tabs

Next to open a new query window you can hit CTRL+N or to open a existing script file hit CTRL+O. You can hit CTRL+TAB to switch between open query windows.

---

More Query Window Shortcuts

Please note apart from the above mentioned shortcut keys these are some standard shortcut keys which work in SSMS as well.

• CTRL+A to select all the text in the current query window
• CTRL+C to copy selected text
• CTRL+V to paste text
• CTRL+X to cut selected text
• DEL to delete text
• CTRL+P to launch Print dialog box
• CTRL+HOME to go to the beginning of the query window
• CTRL+END to go to the end of the query window
• CTRL+SHIFT+HOME to select all text from the current location to the beginning of the query window
• CTRL+SHIFT+END to select all the text from the current location to the end of the query window
• ALT+F4 to close SSMS