Different Ways to Search Database Objects

This article explains different methods to find the database objects in a SQL Database. We can use any of the following methods to search a database object.

• Find database objects using system catalog views.
• Find the database object using the filter option of SQL Server management studio.
• Find database objects using dbForge SQL Search.

Search Database Objects Using System Catalog Views

You can view the list of the database objects from the SQL Server system catalog views. The system catalog views are used to show the information of the database engine. The catalog views can be used to display the meta-data of the SQL Server database. The catalog views inherit the information from the SQL Server metadata tables. For example, sys.tables view inherits the data from sys.objects catalog view.

Following is the list of most-common dynamic management views that can be used to find a database object.

You can read System catalog views to learn more about SQL Server system catalog views and their usage.

We can use SQL Queries with wild cards (LIKE Operator) to find a specific object in a database. Suppose we want to find a table named Posts from the StackOverflow2010 database. The query is the following: 

USE StackOverflow2010
GO
SELECT Name, object_id,schema_id,type,type_desc,create_date,modify_date,durability_desc,temporal_type_desc FROM sys.tables WHERE name ='Posts'

Query Output 

Search Database Objects Using the Filter Option in SQL Server Management Studio

We can use the filter option of the SQL Server management studio. For example, I want to find the tables whose name starts with stock, and the schema is WareHouse from the wideworldimportors database. To do that, expand wideworldimportors --> Right-click on Tables --> Filter à Filter settings.

A dialog box filter setting opens. In the filter criteria, you can specify any of the following parameters

• Table Name
• Schema Name
• Owner
• Durability Type
• Is Memory optimized
• Creation Date

In our case, the table name has the Stock keyword, and the schema name is Warehouse, so I have selected Contains operator in the table name field and the Equals operator in the Schema field. See the following image.

Once the filter is applied, you can see the list of tables whose name starts with the Stock keyword. See the following screenshot. 

The above screenshot shows the list of tables whose name starts with the Stock keyword.

Search Database Objects Using dbForge SQL Search for SQL Server

As I explained in the above examples, we can use DMVs and SSMS to filter the objects, but they have a few limitations.

We can search database objects using dbForge Search for SQL Server tool. It is one of the dbForge SQL Tools used to find any database objects or data from the table, increasing the developers' productivity. For example, a developer works on a large database containing thousands of tables. Now it is difficult to remember the name of all tables. In such cases, the dbForge SQL tool can be handy. All we have to do is to provide a few characters of the table name or the initials of the table name. The tool will automatically populate the name of the database object. Let me explain to you with a simple demonstration.

I have restored the wideWorldImportors database. We want to find a stored procedure whose name starts with configuration. Let us understand how we can find it.

First, open SQL Server Management Studio 15.0 --> Expand SQL Server instance --> Expand Databases --> Right-click WideworldImportors --> Hover on Search --> Select Find Object. 

The object search tab opens. The search tab window is split into three sections. The first tab shows the following details.

• Search Text box: Enter the name or initials of the database objects. The SQL Search will populate the list of objects whose name contains the word/characters specified in a Search text box. In our demo, I specified the configuration keyword.
• Object Type: You can select any of the following object types:
  1. General: Tables, Views, Columns, Indexes,  Constraints, and synonyms.
  2. Programmability: Stored Procedures, Aggregate functions, Scalar functions, table-valued functions, Triggers, etc.
  3. Service Broker: Message type, Contracts, Queues, Routes, etc.
  4. Storage: Full-text Search, Partition Schemes, Partition Functions.
  5. Security: Users, Roles, Schemas, Asymmetric Keys, Certificate Symmetric keys.  

In our demo, I selected the Stored Procedures.

• Database Name: Enter the database name. You can see the list of all databases created in a database instance. We are searching for stored procedures in wideworldimportors; therefore, I have selected wideworldimportors from the drop-down list.
•  Connection Name: Select the hostname.

After specifying all parameters, click on Search to get the list of database objects. See the following image:

As you can see in the above screenshot, the Devart Search populated all stored procedures whose name contains a keyword along with the following details:

• Name: Stored procedure name.
• Owner: Owner of the stored procedure.
• Schema: Schema in which the procedure is created.
• Database Name: If the object is found in multiple databases, you can see the database name in which the object is created.
• Object Type: This column shows the type of database object.
•  Found In: This column shows that the entered keyword is found in the stored procedure code or name of the stored procedure.

Moreover, when you select any stored procedure, it displays the code of the stored procedure.

Summary

This article explains different ways to search a database object within a SQL Server database.