In day-to-day work, DBAs and developers often have to search SQL Server data especially tables that contain specific data. In SQL Server Management Studio (SSMS), or Visual Studio, there is no simple solution to performing such tasks.
Different methods to search SQL Server data article explains some often used methods for searching SQL data using the Where clause with the combination of the Comparison or Logical operators like And, Or, Like, Between, etc.
In this article, how to set up and search SQL Server data using Microsoft SQL Server Full-Text Search feature will be explained and the second part of the article will describe how to find SQL Server data using ApexSQL Search, a third-party tool and its Text search feature for finding SQL Server data in the tables and views.
Full-Text Search
The Full-Text Search feature is an optional component of the SQL Server Database Engine and can be selected during the installation of SQL Server. If the option is not selected during that process, the installation wizard needs to be run again in order to install this component.
To check if the Full-Text Search feature is installed, run the following query:
SELECT SERVERPROPERTY('IsFullTextInstalled')
If the query returns 0, then the Full-Text Search is not installed on the server, but if it returns 1 that means the Full-Text Search feature is installed on SQL Server:
Let’s assume that the Full-Text Search feature is not installed, in which case, the following should be done.
Go to the Start menu, find the SQL Server folder and, click the SQL Server Installation Center command:
Then, under the Installation tab, add the feature for installation:
After the Full-Text Search installation, you’ll be able to set tables to use the Full-Text Search feature and that can be done by using the Full-Text Indexing Wizard which will take you through a series of steps in order to create a full-text index. The same process can be done by using the T-SQL code.
In general, this feature creates a particular type of index referred to as a Full-Text Index. The Full-Text Index can be created on one or more columns for a chosen table. It can be created over the columns that have the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary(max), and FILESTREAM.
Create index using the Full-Text Indexing Wizard
Under the Object Explorer pane, select a table on which want to create a full-text index, right-click and, from the context menu, choose the Full-Text index sub-menu:
As it can be noticed, the Full-Text index sub-menu is disabled. To enable this feature, the Named Pipes protocol under the SQL Server Configuration Manager window needs to be enabled:
Or just check if the Full-Text Search feature is enabled for the database where our table is stored by executing the following query:
SELECT is_fulltext_enabled FROM sys.databases WHERE database_id = DB_ID()
To enable it, execute the following query:
EXEC sp_fulltext_database 'enable';
Now, when all is set, click the Define Full-Text Index command:
The Full-Text Indexing Wizard will be shown, click Next to continue:
Under the Unique index drop-down box, select an index that must be a single-key-column, unique, non-nullable index. For the best performance, the clustered index is recommended:
After the index is chosen, click Next. In the Select Table Columns window, under the Available Columns column, next to the column name, check the box for the column that want to be included for full-text queries:
All other columns (settings) leave as is and click the Next button.
- More information about Full-Text Indexing Wizard options can be found on the Use the Full-Text Indexing Wizard page
On the next page, there are three options of how updates (changes) of tables and views will be tracked:
- Automatically – the full-text index will be updated automatically as soon as the changes in data occur
- Manually – when this radio button is checked, the full-text index will not be updated automatically when changes occur. The update needs to be started or scheduled manually
- Do not track changes – when this option is selected, the full-text index will not be updated when changes in data occur
For our example, the Automatically radio button will be selected:
Under the Catalog, Index Filegroup, and Stoplist window, if exists, select a full-text catalog from the Select a full-text catalog drop-down list or create a new one by checking the Create a new catalog check-box. For our example, the new catalog will be created, and other options will be left as-is:
On the Define Population Schedules window, an indexing population schedule can be defined. This is an optional operation and we will leave it as is and click the Next button:
Next, the Summary window will appear with a list of operations that will be performed by Full-Text Indexing Wizard. Click Finish to continue:
As the last step, the Full-Text Indexing Wizard Progress window will appear:
Now, when all is set, we are ready to use the Full-Text Search feature to search SQL Server data over the Employee table by putting:
- CONTAINS and FREETEXT predicates
and
- CONTAINSTABLE and FREETEXTTABLE row set-valued functions with a SELECT statement
In this article, we will show examples of CONTAINS and FREETEXT predicates.
- For more explanation about Full-Text Search, visit the Hands on Full-Text Search in SQL Server page.
When the requirement is to match (find) words, a phrase, use the CONTAINS and FREETEXT predicates.
CONTAINS
The following example will find all employees whose job title contains the word ‘Engineering’:
SELECT * FROM [HumanResources].[Employee] WHERE CONTAINS ( JobTitle,'Engineering');
FREETEXT
This example will search for all employees whose job title contains words related to ‘Research Engineering’ and variations:
SELECT * FROM [HumanResources].[Employee] WHERE FREETEXT (JobTitle,'Research Engineering');
Search SQL Server data using a third-party tool
ApexSQL Search is an add-in search tool for search SQL Server data and objects for SSMS and Visual Studio.
For searching SQL Server data, ApexSQL Search offers the Text search feature.
To initiate the Text search feature, in the Object Explorer pane, select a SQL Server or select a database from which want to search data, right-click and, from the context menu, choose the Text search command:
The same command can be found under the ApexSQL Search toolbar:
The third way to initiate the search is from the ApexSQL Search main menu in SSMS. Just choose the same option:
The Text search pane will be shown with preset SQL Server and selected database in the Object Explorer pane in the Server and Database drop-down boxes:
In the Search text box, the search string can be specified and, next to this box, there are various filters (options) which can help in defining a search scope:
Under the Select objects to search pane, additionally, the search scope can be narrow down by filtering the tables from one schema:
When all is set, click the Find button. In the search results grid, all values will be shown that are matched to the search scope:
For each found result, additional information is available by clicking the three dots (…) under Column value. The pop-up dialog will appear with information about the row in the table where the record is found:
Note that search scope can be narrowed down to the columns level by selecting the column of interest in the Select objects to search pane:
Moreover, the objects which contain the values from the results can be easily found in Object Explorer by using the Navigate to object explorer node command:
All results from the search result grid can be exported in one of the available formats (XML, CSV, HTML):
Conclusion
Searching SQL Server data is a task that most DBAs and developers perform daily. It usually requires running scripts, modifying code to specify search criteria, etc. but it doesn’t have to be that way. Installing a simple and light-weight third-party add-in as ApexSQL Search allows users to search for database objects in just a few clicks, similar to searching a web using any search engine, and more.
November 16, 2020
