Rajendra Gupta
Object search

Different ways to search for objects in SQL databases

June 29, 2020 by

This article explores various ways to search for database objects in SQL database such as tables, stored procedures, functions, and views.

Introduction

SQL Server has many database objects such as table, view, stored procedure, function, constraints, rule, Synonym, triggers. You being a developer or database administrator might work with thousands of databases. Although you should use a proper naming convention for database objects, it is a difficult task to remember the object names. Let’s consider a single production instance that consists of many databases. You want to look for a specific database object but not sure it exists in which database. It is not possible to explore each database and view the object.

In this article, we explore various ways to search for database objects in the SQL database.

Use sys.objects system catalog view

We can use system catalog view sys.objects to view all objects in a SQL database. It has a column type that contains the object category. For example, if we want to search only for the user-defined table, we use ‘U’ value for the type column.

I filter records for the useful columns. You can get all columns using the select * statement.

Use sys.objects system catalog view

Similarly, we use the value ‘P’ for the stored procedure.

Search Stored procedures

You can also search for objects modified in ‘N’ number of days. In the below query, we try to find output objects changed in the last 60 days in the sample database [adventureWorks].

Search for object as per their modified date

You can refer to Microsoft docs for different values for the Type column in the sys.objects.

Use System Information Schema views

We can also use information schema views to search for the specific SQL database objects. For example, we use information_schema.Tables to find out specific tables in the SQL database.

In the script below, we search for the [Demotable].

System Information Schema views

We can use this schema view to find database view as well if we run above query without a WHERE clause, it returns both SQL tables and views.

System Information Schema tables

Similarly, we can use information_schema.CHECK_CONSTRAINTS to find out check constraints in the current database.

Search CONSTRAINTS in a SQL database

We can use information_schema.SCHEMATA to search for schema and their owners using the below query.

Schema and their owners

You can check the complete list of information schemas using the Microsoft docs.

Search object using SSMS object explorer details

We can use SSMS in-built object search functionality to find out specific objects across all online databases in SQL instance.

Navigate to View-> Object Explorer Details in SSMS. You can use a keyboard shortcut F7 to open it.

SSMS object explorer

It opens the following screen and shows the various folders – Databases, Security, Server objects, Replication, PolyBase, Always on High Availability. You can also see the search box, as highlighted below.

Search box

Search objects in a single SQL database

Here, you can either search the object in a specific database. For that, you can browse the database folder and select the required database.

Search object in a single database

Now, enter the object name in the search box and press enter.

Enter the object  name

It searches the object and gives you a result, as shown below. You also get the path in a PowerShell output format.

View output

Search object in all online SQL databases

You can search for objects in all databases in the connected instance using this object explorer search. On the home page of the object explorer, enter the object name and search.

Search object in all online DB

In the result below, you see that a specified object exists in multiple databases.

View object

You can browse to the specified object in the database using the object explorer. Click on the particular object and navigate to Synchronize.

Synchronize

It takes you to the object, as shown below.

View an object in explorer

Filter objects in SQL databases using SQL Server Management Studio

Suppose you know the database in which the object exists. You can filter objects in SSMS to show only relevant objects to you. For example, let’s say we need to search only tables with [HumanResources] schema in the [AdventureWorks] database.

To filter the tables, expand [AdventureWorks] database, right-click on the tables and click on Filter settings.

Filter objects

It opens the following filter setting page. You have multiple filter options. You need to specify one or more filter conditions. I specify schema [HumanResources] as shown below:

Specify filter conditions

Click Ok, and it filters the results as per the specified condition. You can use equal, not equal and contains an operator in the filter.

Once it applies filters, it changes the folder name as well from tables to tables (filtered). It helps you to recognize that you are viewing filtered objects in SSMS.

Table filter

We can remove or modify the filter to go back to the original states in the SSMS object browser. Go back to tables (filtered) and select the options – Remove Filter to clear the filter or Filter Settings to view filter, change it, if required.

Filter settings

Use ApexSQL Search in SSMS to search for SQL database objects

ApexSQL gives you a FREE tool ApexSQL Search to install in SSMS and Visual Studio for object search.

Use ApexSQL Search

Download and install it in your system to integrate with SSMS or Visual Studio. Once installed, Launch SSMS, Connect to SQL instance and navigate to ApexSQL Search -> Object search.

Object search

It opens the object search windows.

Welcome page

In this window, you can do the following configurations:

  • Search text: Enter the keyword you wish to search
  • Server: It is the SQL instance you connected
  • Database: Here, you can select a single database, multiple databases or all databases
  • Object type: By default, it searches in all the objects. You can expand object types and select the specific objects if we want a specific search result. For example, if we want to search the object in the table and stored procedure, just select the required options

Object type

Let’s search for the objects containing employee keyword, and you get the detailed results.

View results in ApexSQL search

Let’s search for specified object names in the stored procedures and user tables. It gives you the flexibility to view the object definition as well as search results.

Procedures and user tables

You can perform another level of object filter using this ApexSQL Search. Suppose for employee keyword you get 100 results. Now, you want to filter those 100 results as per below:

  • An object should belong to schema [HumanResources]
  • Its name should contain a login word

To set these filters, click on the row below the column name, select the comparison operator and your condition.

Further filtering

It immediately filters the results, and you get an object as per your requirement.

View filter

Right-click on the object, Navigate to object explorer node.

Navigate to object explorer node

It takes you to a specific object in the SSMS.

SSMS object

By default, it does not perform an exact search for the keyword you specified. In case, we want an exact search for the object name specified so we can put a check on – Exact match as shown below.

Exact match

ApexSQL Search is an excellent tool to search for specific objects in all databases of SQL Server. It is integrated well with SSMS so you can easily use it without launching any external program.

Conclusion

In this article, we explored various ways to search for SQL database objects in SQL Server. We can use T-SQL, object explorer search, SSMS filter, as well as third-party tools such as ApexSQL Search for the same. You can choose the appropriate tool and search for objects.

Rajendra Gupta
149 Views