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.