By: Sherlee Dizon | Comments | Related: > SQL Server Management Studio
Problem
Have you ever experienced the need to find a stored procedure, a table or a view in a large SQL Server database where the only thing you remember is part of the object name? I have had that experience and in this tip I will show you a quick way to find database objects using SQL Server Management Studio.
Solution
This filter option in SQL Server Management Studio (SSMS) is very useful if you have lots of tables, views, stored procedures and functions in your database and you need to search for a particular set of objects. As a developer we always aim to be more productive, that's why I like this functionality in SSMS which saves me a lot of time when I need to look for a database object. We will look at a few different examples below.
Filtering a List of Tables in SSMS
1. Right click on the Tables folder and a list of options will pop-up as shown below:
2. Choose Filter > Filter Settings
3. A dialog window will open allowing you to choose your search criteria. For this example let's search using the filter criteria Name. Let's say I want to see all the tables that contain "contact" in their name. In the Value column type contact. This is not case sensitive, so you will not have to worry about uppercase or lowercase. Then click the OK button to apply the filter.
4. Here is a list of objects that contain the word contact. Your list of tables are now filtered with the search result. Notice the word "(filtered)" next to the Tables folder, this indicates the table list is filtered.
5. On the screenshot below you will see other Operator options available for filtering including "Equals", "Contains" and "Does not contain".
Remove Filtered List in SSMS
1. Right click on the Tables folder again.
2. Choose Filter > Remove Filter
3. Notice that all tables are listed again and the text "(filtered)" next to the Tables folder has been removed.
Other Filter Criteria Properties in SSMS
Schema
This is useful if you know a part of the schema name for a set of items you are looking for. The Operator options are "Equals", "Contains" and "Does not contain".
Owner
This is useful if you have different owners and you want to list all tables for a particular object owner.
The Operator options are shown below:
Creation Date
This is useful if you want to list particular tables or stored procedures created for a given date or date range.
These are the Operator options available as shown below.
Combining Filter Options
You can combine the filter criteria for a more complex search requirement. Another thing I've learned is that good naming practices will also benefit you in using this functionality.
Filtering Stored Procedures, Views or Functions in SQL Server Management Studio
You may also use filter settings in your list of Views, Stored Procedures and Functions. Below are the screenshot to show the filter setting options.
Next Steps
- Learn more by exploring and trying different filter options for:
- Filter Views
- Filter Stored Procedures
- Filter Functions like Table-valued, Scalar-valued, Aggregate and System Functions
- Read more SSMS tips
About the author
Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
