This article explains different ways to filter the objects in the object explorer of the SSMS 2016. When we are working with hundreds of databases or hundreds of objects within a database, then it becomes challenging to locate a specific database or database object. To handle such challenges, we can use the filter option in the SQL Server management studio. In the SQL Server management studio, we can apply a filter on the following objects.

1. Databases
2. Tables, Stored Procedures, Functions
3. SQL Jobs and maintenance plans

The following are the filter criteria supported by the SQL Server Management Studio 2016.

Apply filter on the database

Suppose you want to search the database whose name contains the Install keyword. To filter the name of the database from the object explorer, right-click on Databases hover on Filter Select Filter Settings. See the following image:

In filter settings dialog box, the value of the “name” property must be “Install”, and “Operator” must be “contains”.

Once the filter is applied, you can see the list of filtered databases under “databases”.

Another example: Suppose you want to search the database whose owner is “sa” and name contains the “AdventureWorks” keyword. In filter settings dialog box, set the properties as follows:

• Criteria 1: The value of the “owner” property must be sa and the operator is “Equals”
• Criteria 2: The value of the “name” property must be “AdventureWorks” and the operator must be “contains”

See the following image:

As you can see, the databases are filtered based on the given criteria.

Filter database objects

Suppose we want to get the name of the table that contains the “Department” word and it should be in HumanResource schema. To find the table expand the “AdventureWorks2016” database Right-click on “Tables” Hover on the “filter” and select “filter settings”. See the following image:

In filter settings dialog box, set the properties as follows

• Criteria 1: The value of the “Schema” property must be “sa”, and the operator must be “Equals”
• Criteria 2: The value of the “name” property must be “department” and the operator must be “contains”

See the following image:

As you can see, the tables are filtered based on the given criteria.

Suppose you want to populate all the memory-optimized tables. To find memory-optimized tables, set the value of the “Is Memory Optimized” property to “True”

Suppose you want to filter the views that have been created after “26^th December 2019″ and are in “Sales” schema. To find the views expand the “AdventureWorks2016” database Right-click on “Views” Hover on the “filter” and select “filter settings”. See the following image:

In filter settings dialog box, set the properties as follows

• Criteria 1: The value of the Schema property must be “Sales” and the operator is “Equals”
• Criteria 2: The value of the Creation date property must be “26-12-2019” and the operator must be “Greater than”

See the following image:

As you can see, the views are filtered based on the given criteria.

Suppose you want to populate the list of the natively compiled stored procedure from the AdventureWorks2016 database. To find the stored procedures, expand the “AdventureWorks2016” database Expand “Programmability” Right-click on “Tables” Hover on the “filter”, and select “filter settings”. See the following image:

To populate the natively compiled stored procedure, set the value of the “Is Natively Compiled” property to “True”

As you can see, the stored procedures are filtered based on the given criteria.

Filter SQL Server Jobs

Suppose we want to populate the list of SQL Jobs whose name contains the keyword “Backup”, and the owner of the job is “sa”. For that, Expand SQL Server database engine Expand “SQL Server Agent” Right-click on “Jobs” Hover on the “filter” and select “filter settings”.

In filter settings dialog box, set the properties as follows

• Criteria 1: The value of the “Name” property must be “backup”, and the operator must be “contains”
• Criteria 2: The value of the “owner” property must be “sa” and the operator must be “equals”

See the following image:

As you can see, SQL Jobs are filtered based on the given criteria.

Another example: Suppose we want to get the SQL Jobs those are categorized as “Database Maintenance” To filter those jobs; the value of “Category” property must be “Database Maintenance” See the following image:

As you can see, SQL Jobs are filtered based on the given criteria.

Summary

In this article, I have explained how we can apply a filter to identify the specific databases, database objects, and SQL Jobs in SSMS 2016.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022