mssqltips logo

Searching for database objects using SQL Server Management Studio

By:   |   Updated: 2016-05-25   |   Comments   |   Related: More > 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:

Filter option for tables in SQL Server Management Studio

2. Choose Filter > Filter Settings

Filter settings for tables in SQL Server Management Studio

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.

Filter settings for tables in SQL Server Management Studio including name, schema, owner and creation date

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.

Filter tables in SQL Server Management Studio

5. On the screenshot below you will see other Operator options available for filtering including "Equals", "Contains" and "Does not contain".

Operator Options for filtering in SSMS

Remove Filtered List in SSMS

1. Right click on the Tables folder again.

2. Choose Filter > Remove Filter

Remove Filter in SQL Server Management Studio

3. Notice that all tables are listed again and the text "(filtered)" next to the Tables folder has been removed.

Original Table List in SQL Server Management Studio

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

Filter By Schema in SQL Server Management Studio

Owner

This is useful if you have different owners and you want to list all tables for a particular object owner.  

Filter By Owner in SQL Server Management Studio

The Operator options are shown below:

Owner Operator Options in SQL Server Management Studio

Creation Date

This is useful if you want to list particular tables or stored procedures created for a given date or date range.

Filter By Create Date in SQL Server Management Studio

These are the Operator options available as shown below.

Create Date Operator Options in SQL Server Management Studio

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.

Filter Views in SQL Server Management Studio

Filter Stored Procedures in SQL Server Management Studio

Filter Functions in SQL Server Management Studio
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


Last Updated: 2016-05-25


get scripts

next tip button



About the author
MSSQLTips author Sherlee Dizon Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools