Are You Using SSMS effectively ?

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

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


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.




Equals, Contains, Does not contain


Equals, Contains, Does not contain


Equals, Does not contain


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

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

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


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

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.

Pages: 1 2


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |