SQL SERVER – Free Database Search and Dependency Analysis

This article covers the main features of ApexSQL Search, a free SQL Server Management Studio and Visual Studio add-in for SQL Server database object and text searching and for dependency analysis. Let us learn about Free Database Search and Dependency Analysis.

You can download the most recent version of ApexSQL Search from this link and play along through the article.

Database object search

ApexSQL Search will show all results based on the specified search, no matter if it is a part of the object name, or object body. Even system objects can be searched. To initiate the object search, right click the database or any object from the Object Explorer pane and from the ApexSQL Search menu, select the Database object search option:

The same option is available from the ApexSQL Search main menu, assuming that the database that will be search is selected in the Object Explorer:

This will initiate the Database object search tab:

For the specified search, the user can set the search options, so the results will include all matches from the object name, object body, system objects, or by searching for an exact match:

Using the Object type filter, searching can be narrowed down to the specific object types (e.g. tables):

For each of the search result, the user can perform variety of commands, using the right-click context menu:

ApexSQL Search can navigate to the specific object from the results grid to the Object Explorer using the Navigate to object explorer node option.

The content of each cell from the results grid can be copied to clipboard using the Copy cell option.

Complete list of search results can be copied to clipboard or saved in one of the available formats:

Dependency Viewer

In addition to manipulating with the search results, within just a single click the user will be able to review all dependencies of the selected object, by using the View dependencies option. The option is available on each individual object, on a database level:

The same option is available from the ApexSQL Search main menu (assuming that the corresponding database or object is selected in the Object Explorer pane):

This will initiate the Dependency viewer form:

Besides the main grid where the graphical representation of object dependencies is shown, there are four main ‘panes’ to manage the dependency appearance and accuracy:

• Object filter (upper left) – narrowing down the scope of objects type to be shown in the diagram
• Object browser (lower left) – filtering through the individual object list, grouped by the object type. In addition to this, using the filter row, the user can easily find an object, or schema
  
• Dependencies – (lower right) – for each selected object in the main grid (i.e. for the ufnGetProductDealerPrice SQL function) all dependent objects will be listed
• Overview – (upper right) – gives a general overview of the whole diagram and the current zoomed part shown in the main grid

Database text search

Similar as the Database object search option, an option for text search is available from the Object Explorer pane, by right-clicking the database, or any of the object, or in the ApexSQL Search main menu:

This will initiate the Database text search tab:

There are variety of options to set before the search process is started. Besides this, the user can narrow down the scope of the search by selecting specific tables from the Select objects to search pane to the left. The user can search through system objects, include empty fields as well as null fields.

From the search results the same context menu is available as explained in the database object search part.

The only difference is that ApexSQL Search offers additional details for each of the search results in the database text search results grid, by clicking the ellipsis button for the specific search result:

This will give an overview of the exact row in a table where the selected result is found.

Safe rename

This option allows the user to rename tables, views, stored procedures, functions, columns and parameters without breaking any dependencies. Using the Safe rename option, an object can be moved from one schema to another:

ApexSQL Search will create a script that will be used in the renaming process. The script can be reviewed and changed additionally in the query window. All actions that will be executed prior to the renaming will be shown in the Sequence tab of the Safe rename form.

Extended property editor

The Extended property editor feature allows you to review the existing and to add a new extended property. To initiate the Extended property editor, select the Edit extended properties option, either from the Object Explorer right click menu or from the ApexSQL Search main menu:

To access the extended property of the specific object, click the ellipsis button, and review or modify the extended property. Once edited, the extended property will be saved directly in the database:

Reference: Pinal Dave (https://blog.sqlauthority.com)