Ask yourself one question, why do we need a SQL search tool? You might not think of it as a big deal, but imagine not having a web search engine. How would anyone surf the web, right? Having the ability to find information quickly is a must-have in today’s world because it makes our lives easier.
As database developers, we need a way to be more productive, work faster, increase efficiency, and reduce mistakes to a minimum. The easiest way to accomplish all of that is by using the right tools.
This article is an overview of one such tool that enables users to quickly search for SQL objects and data directly in SQL Server Management Studio and Visual Studio, so we no longer need to waste time running complicated scripts.
Introduction
ApexSQL Search is a neat solution that helps users simplify some database-related tasks, specifically those of searching for SQL objects and data, handling extend properties, renaming database objects without breaking dependencies, etc. even if you are not the most tech-savvy of users.
It’s a fairly simple and lightweight SQL search add-in that, once installed, it creates a separate menu (ApexSQL) within the host application, thus making it easy for users to select the type of search they want to perform.
Searching for objects
The Object search feature allows users to find SQL objects containing a specified phrase.
To launch an object search, go to ApexSQL menu within the host application, navigate through ApexSQL Search, and then select Objects search option:
This will open the Object search tab in which users can choose a server, database, and types of objects to search for. Furthermore, to narrow down a search, additional filters on the right can be selected:
-
Tip: Hovering the mouse over the icon near each filter will display a brief explanation of what the options do. Use this information if not familiar with how a filter will affect the search.
Once the search scope is all set up, simply enter the search phrase in the Search text box, and click Find or hit the Enter key to initiate the search:
When the SQL search is done, the status bar (colored green) at the bottom indicates the number of total hits. Returned results are divided into groups based on object types. Additionally, each hit displays information like which schema and database an object belongs to, object type, wherein the name was the keyword found, preview, and object definition.
Furthermore, by selecting an object from results, e.g., a function, the Object preview window will pop-up at the bottom showing full object’s definition:
This is very useful when browsing thought stored procedures, functions, views, etc. because there’s no need for scripting those objects from Object Explorer just to see their definitions.
Those types of objects can have some complex logic and many lines of code. Therefore, there’s a find option right in the Object preview window. Just right-click anywhere within it, choose Find, type the keyword, and found results will be highlighted in the script:
SQL search doesn’t stop there. Back to results, there’s a right-click menu with a variety of options.
For example, finding database objects in Object Explorer sometimes requires a lot of clicking and browsing. However, with the help of this add-in, any object from the results set can easily be located by choosing the Navigate to object explorer node option which will basically jump to that object in Object Explorer:
All other options are pretty much self-explanatory except the Safe rename that will be covered later in the article.
Search results can have thousands of hits. Take advantage of results browsing by grouping, sorting, and filtering SQL search results. All these options are available from the column names:
Searching for data
The Text search feature allows users to find data in tables and views matching the specified phrase. To launch a text search, go to ApexSQL menu within the host application, navigate through ApexSQL Search, and then select the Text search option:
This time, the Text search tab is opened in which various filters can be set to define a search scope. This type of SQL search is also known as data search because it searches for the actual data within database objects:
Instead of choosing database object types, data search offers the selection of tables and views to be searched for. For example, to narrow down a search, only tables and views from one schema can be selected under Select objects to search pane:
Same as with object search, when the scope is all set up, simply enter the search phrase in the Search text box, click either Find or hit the Enter key to initiate the search, and the search results will be displayed:
Notice that search results are relatively the same as with object search. The same applies to the right-click context menu, and its options, e.g., quick navigation works in both features. The only difference between object search and data search is that in here, additional information is available for each found result that can be accessed by clicking the three dots (…) under Column value:
This action will pop-up a window that has detailed information on the exact row in the table or view where the record is found.
Renaming database objects
Have you ever wanted to rename columns but aren’t certain what stored procedures reference it or simply was too afraid to break dependencies?
Look no further for a solution because this SQL search add-in has just the right tool for the job. The Safe rename feature is designed to help users with changing the name and schema of tables, procedures, views, functions, columns, etc. without breaking dependencies.
Pick any supported object from Object Explorer, right-click on it, and choose the Safe rename option:
-
Tip: The same process can be initiated from the list of found results for both object and data search by clicking on an individual result and choosing the Safe rename option.
In the newly opened Safe rename window, specify a new name for the object and click the Preview script button to generate a preview of rename script, warnings, summary, and dependencies:
Next, go through all the tabs and review warnings, if any, don’t just ignore it like some people do with the check engine light in a car.
The generated code for renaming an object is wrapped around BEGIN…END transaction, so if anything goes wrong, the whole thing will be rolled back. However, similar to the onboard diagnostics system in a car, the warnings can give us insight into what could potentially go wrong when renaming an object in a database.
The Sequence tab displays all actions that will be executed prior to renaming the object:
Lastly, the Dependencies tab shows all objects that depend on the object to be renamed, and those will be dropped and recreated to not break dependencies in the process:
Once everything is reviewed and ready to be executed, just click Create script to open the script for renaming in the query editor. From there, just hit Execute to finalize the process. Refresh content of Object Explorer to see changes:
Just like that, this SQL search add-in renames database objects risk-free.
Searching history
The add-in is saving search patterns automatically so it can be selected next time (like internet searches) quickly.
To rediscover the things you’ve searched for, open the drop-down list under the Search text box and choose an item or start typing what you want to search for and it will show up as a hint if it was used in a search before:
The activity is different for objects and data searches. The SQL search add-in keeps track of both but separately.
-
Tip: Some activity may not appear right after a search is performed. However, all activity during a session is logged and will be available on the next launch of the host application.
For more information and searching tips, see Search phrases, history, and results.
Editing extended properties
The extended properties editor allows users to view, edit, and add descriptions for database objects.
To initiate the extended properties editor, right-click a database or object in Object Explorer, and select Edit extended properties:
Give it a few seconds, and Extended property editor will open up from where the users can view and edit any extended properties that are stored in SQL Server database::
To open up the extended property of an object, select it under the Description column and click on three dots (…) that will appear on the right:
Any changes made in the text editor and the OK button is clicked, changes are automatically saved in the database. Reset option will discard all changes made in the text editor, and both Cancel and Close (x) will do the same but also close the dialog.
If you’re having trouble finding anything here, use the Find option, type text to search, and hit Enter. The SQL search add-in will highlight all matches found in all columns:
-
For detailed information about displaying descriptions for the database objects, see How to use the ApexSQL Search extended property editor.
Conclusion
Everything considered this is a nifty SQL Search add-in that integrates into both SSMS and VS. It helps the users speed up a lot of database-related tasks. In this article, we’ve learned how to use data search and schema search, highlight found objects, save search patterns automatically so they can be selected next time, etc.
We hope you found this article helpful. Happy searching!
July 24, 2020
