SQL Server database object search

Developers and DBAs often perform database searches in order to find and obtain additional information related to specific objects, sometimes without knowing the full name of the object.

SQL Server provides sys.objects system view that list all the objects from the database.

SELECT *
FROM sys.objects

Which will return all user-defined (tables, view, functions) and system objects in that database:

To find desired SQL Server database objects, type the following:

USE AdventureWorks2014

SELECT NAME AS ObjectName
	,schema_name(o.schema_id) AS SchemaName
	,type
	,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
	AND o.NAME LIKE '%Add%'
ORDER BY o.NAME

This will provide a list of all user-defined objects (is_ms_shipped=0), which names contain Add characters:

Quick tip: sys.objects does not show DDL triggers. To view all DDL and DML triggers use the sys.triggers system view.

SQL Server Management Studio provides the Object Explorer Details feature that allows searching for any object in a database which is chosen in the Object Explorer pane.

To open the Object Explorer Details panel, press the F7 key or from the View menu choose the Object Explorer Details option:

Which will open the Object Explorer Details panel:

To find SQL Server database objects first in the Object Explorer panel, select the database over which wants to search the objects and in the Search text box from the Object Explorer Details panel type in the search criteria and press enter. This will return all user-defined and system objects that match with chosen criteria:

Right-click on an object from the list in the Object Explorer Details panel, will popup menu with the Synchronize option, which leads directly to the location of the object in the Object Explorer panel:

The Go to object feature

ApexSQL Complete is SSMS and VS add-in that speeds up coding, and improves productivity using features such as, auto-completing SQL keywords and code fragments, SQL snippets, auto inserting statements, text auto-replacement, Go to object and more.

To perform a quick object search over a database in order to find SQL Server database objects and get the appropriate information (e.g. how many tables have the same name in the database with different schemas) or to locate objects in the Object Explorer panel in SSMS, the Go to object feature can be very useful.

There are three ways to initiate the Go to object feature:

1. From the Object Explorer, select the desired database and from the ApexSQL Complete tool bar, choose the Go to object button:



2. When the query window is opened, from the Available Database combo box in the SQL Editor toolbar, select the desired database and click the Go to object button from the ApexSQL toolbar:



3. Use the Ctrl+Alt+Y shortcut

The Go to object window will be shown with all objects from chosen database in the display list:

Quick tip: The Go to object feature lists only user-defined objects

As soon as the user starts to type some text in the text box, the appropriate objects will be shown in the display list and on the right side of the text box will appear the number of the objects that matches with criteria that is typed in the text box:

Additionally, the Go to object feature provides the option to list only the desired object types (e.g. tables). To do that, from drop-down menu, which is located on the upper right side of the display list, select the desired object type:

Double-click on the object in the list or right-click on the object and from the popup menu choose the Go to object command, and the chosen object will be shown in the Object Explorer panel:

The Navigate to object feature

To find SQL Server database objects in the Object Explorer panel via the query window, right-click on the name of the object in the query window and from menu choose the Navigate to object command:

Another object search feature

ApexSQL Search is add-in for database objects and text searching, which integrates with SSMS and Visual Studio.

To perform more complex database object searches and find desired SQL Server database, over one or all databases, the Object search can be very handy.

There are two ways to initiate this option. Right-click on the database or any object from the Object Explorer panel and from the context menu, choose the Object search command:

Another way to initiate the same option is from ApexSQL Search menu, assuming that the database which will be searched is selected in the Object Explorer panel:

This will open the Object search query window:

In the Search text drop-down box any type of strings can be entered, including strings that contains special or Unicode characters. T-SQL wildcards can be also used as part of the search phrase. For instance, to find objects for which only the part of the name is known, typing the %Add% and press enter or the Find button, the Object search query window will find SQL Server database objects that contain this phrase in the name of the objects:

Quick tip: The Search text drop-down box is case insensitive. For example, the result for the search phrase ADDRESS or address will be the same.

Additionally, the search phrase can be adjusted by specifying whether to match (e.g. Exact match, Object name, Object body) to the searched text:

The Search text drop-down box keeps all committed phrases and this is quite handy when used one phrase over and over again:

The Object search allows users search over the all databases objects on the SQL Server by choosing the All databases option under the Database combo box:

Quick tip: Searching in all databases can be time consuming so make sure to apply rigorous filters if possible

The Object search has the option to quickly identify the desired results by filtering out unwanted results using the Filter row option. Right-click on result grid and from the popup menu choose the Filter row command:

This will open filter row on top of the grid, where each field in the filter row contains values from the appropriate column:

By default, when it opens the Object search query window the filter row is active.

ApexSQL Search supports regular expressions in order to find required results.

In addition, to show the results from two databases (e.g. AdventureWorks2014 and WideWorldImporters), in the Database combo box, choose the All databases option and press the Find button. After the results are shown, in the Text Filters tab type 2014 or Wide::

To navigate to the specific object in the Object Explorer, from the Object search results grid right-click on the desired object and from the popup menu select the Navigate to object explorer node option. The same result can be achieved by double-click on the object from the result grid: