Now that all my server are SQL Server 2008 I use SSMS with SSMS Toolpack and Toad. I don’t really use Query Analyzer anymore. The other day I found out that you can hide objects you don’t want to see in SSMS by using filters.

Let’s first look at some code. Create a new database named test, create a new schema named Denis and then add 3 tables to the dbo schema and 3 tables to the Denis schema. Just run the code below

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create database test
go
 
use test
go
 
create schema Denis
go
 
create  table Test1(id int);
go
create  table Test2(id int);
go
create  table Test3(id int);
go
 
 
create  table Denis.Test1(id int);
go
create  table Denis.Test2(id int);
go
create  table Denis.Test3(id int);
go
create database test
go

use test
go

create schema Denis
go

create  table Test1(id int);
go
create  table Test2(id int);
go
create  table Test3(id int);
go


create  table Denis.Test1(id int);
go
create  table Denis.Test2(id int);
go
create  table Denis.Test3(id int);
go

Now in SSMS you will see the following if you expand the tables folder

What if I only want to see the tables that belong to the Denis schema? Here is how you can setup the filter. Right click on the tables folder, select Filter and then Filter settings.

That will pop up a window that looks like the image below

For the Schema property select equals for the operator and type Denis as value. Click OK and now you will see the following when you navigate to the tables folder.

If you want to remove the filter, just right click on the folder, select Filter and then Remove Filter.

If you work in a team and you created several tables that all start with Report then you can create a new filter and just show these objects. This way you don’t need to see any tables that your team members created. Below is a screen shot of a filter like that.

Another cool feature is that you can filter on Creation Date, this gives you the ability to filter all that old Brownfield stuff that nobody maintains anyway because if you change one thing it will break everything else 🙂

So are you using filters in SSMS?

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum