Nisarg Upadhyay
Filter settings criteria for SQL Jobs in SSMS 2016

How to filter objects in SSMS 2016

March 5, 2020 by

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.

Object type

Filter criteria

Databases (SSMS 2016 and above)

  • Name of a Database
  • Owner of a Database
  • Creation date of a Database

Database diagrams

  • Name of a database diagram
  • Owner of a database diagram

Tables

  • Name of a table
  • Schema of a table
  • Owner of a table
  • Durability type (In-Memory) of a table
  • Is table memory-optimized
  • Creation date of a table

Views

  • Name of a view
  • Schema of a view
  • Owner of a view
  • Creation Date of a view

Stored procedures, table-valued functions, aggregate functions and scaler-valued functions

  • Name of the Procedure/function
  • Schema of the procedure/function
  • Owner of the Procedure/function
  • Is procedure/function natively compiled
  • Creation date of the Procedure/function

SQL Jobs

  • Name of a SQL Job
  • Owner of a SQL Job
  • Category of a SQL Job
  • An originating server of a SQL Job
  • Creation date of a SQL Job

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:

Filter database in SSMS 2016

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

Database Filter setting

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:

Database filter with multiple conditions

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:

Table Filter in SSMS 2016

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:

Table filter setting

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”

Table filter condition

Suppose you want to filter the views that have been created after “26th 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:

Filter Views in SSMS 2016

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:

Filter settings for view

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:

Filter Stored Procedures in SSMS 2016

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

Filter settings for Stored Procedures

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”.

Filter SQL Jobs in SSMS 2016

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:

Filter settings for SQL Jobs

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:

Filter settings criteria for SQL Jobs in SSMS 2016

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.

Nisarg Upadhyay
SQL Server Management Studio (SSMS)

About 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

5,983 Views