Searching for Database Objects and Table Data in SQL Server

If you are a SQL Server database administrator or developer, you need to frequently search for specific database objects the names of which you can only partly remember. The manual approach to performing these search operations is time-consuming, especially when the database has a large number of objects.

Another scenario is one in which you want to search for specific data in all database tables. In this case, you need to run a SELECT statement multiple times based on the number of database tables or views you want to search in, or write a complex cursor to loop and search in all database tables, which requires advanced SQL Server development skills.

In this article, we will look at different ways of searching for a specific database object or data in SQL Server, going gradually from built-in methods and diving into a 3^rd-party tool that makes it a piece of cake to get the requested database object or data.

Querying sys.objects

Sys.objects is a SQL Server system dynamic management view that can be used to list all objects that are defined under a specific database.

For example, to list all database objects that have “Emp” in their names, we can query the sys.objects system DMV based on the object name, as in the T-SQL query below:

SELECT NAME AS ObjName
    ,schema_name(schema_id) AS SchemaName
	,type AS ObjType
	,type_desc AS ObjTypeDesc
	,create_date
	,modify_date
	FROM sys.objects
WHERE NAME LIKE '%Emp%'
ORDER BY ObjName ASC, ObjType ASC

And the result returned from my testing database, with complete information about the full object name, type, creation and modification data, will be as below:

SQL Server Management Studio Object Explorer

Another method that can be used to search for a specific database object in your database is the SQL Server Management Studio Object Explorer.

When using SQL Server Management Studio to connect to and administrate your SQL Server instance, you can open the Object Explorer by choosing the Object Explorer Details option under the View menu, or simply pressing the F7 key, as shown below:

When the Object Explorer is opened, you can search for a specific database object in the following way:

• browse to the database you want to search through
• write the name (full or partial) of the database object in the Search text box
• press Enter to start the search process

The list of all database objects that meet your search criteria will be displayed as shown below:

To go to the location of a specific database object, from the opened Object Explorer search result, right-click on that database object and choose the Synchronize option, as shown below:

The pointer will automatically lead you to that object’s location as shown below:

SQL Server Management Studio Filtering

SQL Server Management Studio provides us with another way to search for and locate a specific database object using Filters.

Database objects can be filtered as follows:

• browse to the database where the objects are located
• expand the database node
• point to the folder in which the object is located (such as Tables, Views, Stored Procedures or Functions)
• right-click on that node and choose Filter Settings under the Filter options list, as shown below:

From the opened Filter Setting window, choose the search criteria that you will use to identify the database object (e.g. name, schema or the owner of that object, the operator that will be used in the search filter). For example, you can search for a database object with the name equals to, containing or not containing the provided value. After specifying the filter criteria, click OK to apply the filter:

Once the filter is applied, you will see that only the database objects that meet the filter search criteria will be listed under the objects type node, as shown below:

To remove the filter and view all the database objects again, right-click on the relevant database objects list node, which is the Tables node in our example, and choose the Remove Filter option from the Filter options list, and all database objects will be displayed again, as shown below:

Data Search in SQL Server Management Studio

In addition to writing SELECT queries to search for a specific value, SQL Server Management Studio provides us with another way to search for column values and modify them directly at the database object level based on the specified criteria.

To search for a data value from a specific table, browse to that table in the SQL Server Management Studio, right-click on that table, and select the Edit Top 200 Rows option, as shown below:

From the opened window, click the Table icon shown below in order to view the filtering window:

In the opened filtering window, specify the condition and the column(s) you are interested in. In the example below, we will list all employee rows with ID larger than 10:

After setting up the filter, right-click anywhere in an empty area and choose the Execute SQL option to apply the conditions:

The rows that meet the applied conditions will be shown in the result grid, and you can perform updates on this data, as shown below:

Search for a SQL Server 3^rd-party tool

Until this point, we discussed different methods of searching for database objects and table data. But these methods provide us with specific types of information that cannot be filtered, sorted, or managed easily. This is why we need to look to 3^rd-party tools available in the market, and hopefully find one that provides all the discussed search functionality in the same window and allows us to manage the results based on our requirements.

One of the most useful and user-friendly 3^rd-party tools that can be easily and quickly used to search for any type of database objects or data is dbForge Search for SQL Server by Devart.

dbForge Search for SQL Server is a free SQL Server Management Studio add-in that provides us with the ability to locate any SQL Server database object or data, without the need to look through the SSMS Object Explorer or recall the location of that object. It allows us to search in all database objects or set up our own search filters to quickly locate database objects or data and easily navigate them in the SSMS Object Explorer. With dbForge Search for SQL Server, you can also sort and filter the search results as desired.

The dbForge Search for SQL Server free tool can be downloaded from the Devart download page and easily installed to your machine using a straight-forward installation wizard, as shown below:

The installation wizard will first ask you to specify the installation folder for dbForge Search for SQL Server:

Then it will let you choose the SQL Server Management Studio version installed on your machine:

And finally, you can choose what startup optimization will be applied to the tool to enhance its performance, as shown below:

When the dbForge Search for SQL Server installation starts, you can monitor the installation process via the installation progress bar:

After completing the dbForge Search for SQL Server add-in installation process, the installation wizard will notify us that the add-in is installed successfully for the selected SSMS version and ready to be used:

To start using the dbForge Search tool, open SQL Server Management Studio. You will find the search tool’s unique icon, as shown below:

Click the dbForge Search icon and a new window will be opened, providing you with the option to search both for database objects and table data:

Database Objects Search

Assume that we need to search for all database objects containing “emp” in their names under the AdevtureWorks2016 database.

Click the DDL button at the left to choose the Search for database objects option, write the search phrase into the search box, then select the SQL Server instance that contains that database and the name of the database(s) to perform the search on:

After that, select the type of the database object(s) you want to search for in the Object Types drop-down list:

And finally, specify the type of search you will perform. You can search for database objects with the exact provided word case, the objects that match the whole provided word, or use wildcards (for example, ‘%’) to return objects with names containing the provided phrase:

After clicking on the search button near the provided text, you will see the results displayed in two sections. The first section contains full details about all database objects that meet the provided search criteria. In the second section, you will find the T-SQL statement that is used to create the selected database object:

In addition to the ability to sort the returned result based on any column, the dbForge Search tool provides us with the ability to group the search result by the object’s type. To group the search result by each database object, click on the Grouping icon, as you can see below:

The search result will be grouped by database object types, which makes it easier to locate and manage the objects you are interested in:

After selecting the necessary database object from the search results, you can easily locate that object in the SSMS Object Explorer by right-clicking it and selecting the Find in Object Explorer option:

Data Search

The dbForge Search tool also allows us to search for a specific text or number value in database tables or views. To change the search mode from database object search to data search, click the Data search button at the left side of the window, select the SQL Server instance where your database is hosted, and the database(s), Tables and Views to search the data in. After specifying the data search criteria and filters, write the data value that you will search for and click on the search icon near the provided text, as shown below:

Again, the result will be displayed in two sections. The first section displays full information about the location of the returned value and the second section displays the complete row that contains that value:

From the previous examples, we can clearly see how easy and fast the dbForge Search tool is in searching and locating any database object or data, taking into consideration that the search process will be faster when we provide accurate and narrow search criteria. Try it out for yourself!