When working with a large number of databases or objects within a database in SQL Server, it can be challenging to locate specific items. However, SQL Server Management Studio (SSMS) provides various ways to filter objects in the Object Explorer, making it easier to find what you need.

Filtering Databases

To filter databases in the Object Explorer, follow these steps:

1. Right-click on “Databases” and hover over “Filter”.
2. Select “Filter Settings”.
3. In the filter settings dialog box, specify the filter criteria such as the name of the database, owner, or creation date.
4. Click “OK” to apply the filter.

For example, if you want to search for a database with the name containing the keyword “Install”, set the “name” property to “Install” and the operator to “contains”. Once the filter is applied, you will see the list of filtered databases under “Databases”.

Filtering Database Objects

You can also filter specific database objects such as tables, views, stored procedures, and SQL jobs. Here’s how:

1. Expand the desired database in the Object Explorer.
2. Right-click on the object type (e.g., “Tables”, “Views”, “Stored Procedures”) and hover over “Filter”.
3. Select “Filter Settings”.
4. In the filter settings dialog box, specify the filter criteria for the object, such as the name, schema, owner, or creation date.
5. Click “OK” to apply the filter.

For example, if you want to find a table with the name containing the word “Department” in the “HumanResource” schema, set the “Schema” property to “HumanResource” and the “name” property to “Department” with the operator “contains”. The tables will be filtered based on the given criteria.

Filtering SQL Server Jobs

To filter SQL Server jobs in the Object Explorer, follow these steps:

1. Expand the SQL Server database engine in the Object Explorer.
2. Expand “SQL Server Agent” and right-click on “Jobs”.
3. Hover over “Filter” and select “Filter Settings”.
4. In the filter settings dialog box, specify the filter criteria for the job, such as the name, owner, or category.
5. Click “OK” to apply the filter.

For example, if you want to find SQL jobs with the name containing the keyword “Backup” and owned by “sa”, set the “Name” property to “Backup” with the operator “contains” and the “owner” property to “sa” with the operator “equals”. The SQL jobs will be filtered based on the given criteria.

Summary

In this article, we have explored the different ways to filter objects in SQL Server Management Studio. By applying filters based on criteria such as names, owners, schemas, and creation dates, you can easily locate specific databases, database objects, and SQL jobs within SSMS.