Are You Using SSMS effectively ? (By Dinesh Asanka)

SQL Server Management Studio is an essential tool for DBAs and developers. But are you using it effectively? In this article, I will try examine a few tips and techniques which could well boost your efficiency using SSMS.

Object Filtering

The below screen is probably familiar to most DBAs – it lists all tables under a database. If you want to check something on the Address table, you need search the entire list. Things will be much more difficult if you have multiple schemas, since table is listed according to the order of schemas.

.

You can filter the objects, by following given steps.

First, right-click the Tables node and select Filter > Filter Settings:

Then you will receive below dialog enabling you to filter by any of four parameters – Name, Schema, Owner and Creation Date:

The below are are the operator and parameters you can use for filtering.

Operator	Parameter
Name	Equals, Contains, Does not contain
Schema	Equals, Contains, Does not contain
Owner	Equals, Does not contain
CreationDate	Equals, Less than, Less than or equal, Greater than, Greater than or equal, Between, Not between,

In this example we will use Contains > Address for filtering:

If you set a filter as above, you will end up with the below screen.

As you can see that, your table list now consists only of tables which contain Address.

Object Information

As developer, you will need information on your SQL Server objects. For example, you may need a row count for tables. There are numerous DMVs you can use and if your tables are small, you can simply use T-SQL such as COUNT(*) – I don’t recommend though . However, SSMS can also be used to get those details.

In the main menu, select Object Server Explorer:

Right-click the header, and you will be shown information you can select such as Data Space Used, Index Space Used , row count etc. You will be shown a view like below where all the information you selected is tabulated.

Register Server

How do you login into SQL Servers? Are you entering servername, userid , password every time? This won’t be an easy task if you are managing more than 10 servers. If you can remember back to SQL Server 2000, you had a tool called Enterprise Manager in which you are required to register your server. So when you are logging in again, the saved user name and password will be used. With the introduced of SQL Server 2005, what happened to that nice little feature? It is still there folks!

You can access all the servers that are registered by selecting View > Registered Servers as shown below. To add new servers to a group, simply right-click the group you wish to add a server to and select New Server Registration.

This not only it saves your credentials, but you also will have the option of querying among all your servers.

Let us say you want to list out all the databases.

You can right click any node in the registered server window. In this example I right-click the Dev node in the registered servers and select new query, note the query window is slightly different from one you normal see.

Not only is the color of the footer bar pink but you can see there is a 2/2 label which means that you are connected to two server out of two available.

Let me execute:

SELECT * FROM sysdatabases

This has the following output:

Now you can see all the databases in both servers are listed here. Logins, servers are other things you can list out like this.

Template Explorer

Let us say you want write a DDL trigger but you do not remember the correct syntax for creating a DDL Trigger (which is different than a regular trigger).

You can use the Template Explorer to assist you in this. The Template Explorer launched by selecting View > Template Explorer. This provides a very comprehensive listing of T-SQL templates for common tasks.

If you double click the template you want , relevant template will be opened.

By clicking Shift+Ctrl+M, you will get the option of entering template parameters, so that those can be entered in once place.

You can create your own template with parameters using the following format.

In the above example, database_name is the parameter name, sysname is the data type and the adventureworks is the default value.

Short Cuts

There are a few task that you will most likely perform daily such as viewing table structures or viewing indexes. You can create customized short cuts for these tasks. Select Tools > Options > Keyboard and you will be taken to following window.

By way of example, when analyzing your table structure, indexes you could do this by going to the object explorer and expanding the table. However, this is time consuming since to obtain the table structure, index information you need to navigate several pages. Instead simply hit ALT + F1 and you will see all the relevant information for that particular table. Note that for this short cut might have an issue, if you are using not default schema (ex:dbo), for these cases you need your objects to be enclosed with quotes. ( ‘ HumanResources].[Employee]’).

Ref: http://www.sql-server-performance.com/2011/using-ssms-effectively/