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 returns all user-defined (tables, view, functions) and system objects in that database:

To find SQL Server database objects in which you are particularly interested, 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 icon

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 pane, press the F7 key or from the View menu choose the Object Explorer Details option:

Which will open the Object Explorer Details pane:

To find SQL Server database objects first in the Object Explorer pane, select the database over which wants to search the objects and in the Search text box from the Object Explorer Details pane 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 pane, will popup menu with the Synchronize option, which leads directly to the location of the object in the Object Explorer pane:

The ‘Go to’ object feature

ApexSQL Complete is a free 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 pane 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

Will open the Go to object form with all objects from chosen database in the display list:

Quick tip icon

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 object types for which you are particularly interested (e.g. tables). To do that, from drop down menu, which is located on the upper right side of the display list, select the object type of interest:

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

The Navigate to object feature

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

Another object search feature

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

To perform more complex database object searches and find SQL Server database objects that suit your needs, over one or all databases, the Database 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 pane and from the ApexSQL Search menu choose the Database object search option:

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

This will open the Database object search tab:

In the Search text drop-down box can be entered any type of strings, including strings that contains special or Unicode characters. T-SQL wildcards can be also used as part of the search phrase. For instance, to finds objects that we know only the part of the name, typing the %Add% and press enter or the Find now button, the Database object search tab will find SQL Server database objects that contain this phrase in the name of the objects:

Quick tip icon

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, this is quite handy when used one phrase over and over again:

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

Quick tip icon

Quick tip:

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

The Database object search has the option to quickly identify the results that you are particularly interested by filtering out unwanted results using the Filter row option. Right click on result grid and from the popup menu choose the Filter row option:

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 Database object search tab the filter row is active.

ApexSQL Search supports regular expressions in order to find required results. For example, to show the results from two databases (e.g. AdventureWorks2012 and AdventureWorks2014), in the Database combo box, choose the All databases option and press the Find now button. After the results are shown, in the Database filter row type 2012|2014:

To navigate to the specific object in the Object Explorer, from the Database 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:

See more:

April 11, 2016