Searching for Database Objects and Table Data in SQL Server

Let’s look into the intricacies of database data and object search in SQL Server and see how to conduct it with standard scripts and with a specific tool.

Photo from Piqsels (CC0)

Hello and welcome to the fourth part of my series of articles that will help you discover how to design and work with databases. This time we are going to delve into an intricate matter of data and object search in SQL Server, which will be further conducted first with the help of standard scripts and after that, using the dbForge Search tool.

Quite often we experience the situation when we need to find:

1. A database object (a table, a view, a stored procedure, a function, etc.).
2. Data (value and the table that contains it).
3. A code fragment in the database object definitions.

Data and object search in the SQL Server using standard scripts

Let us first show how to do this with the help of standard scripts.
You can search for the Employee table in the database with the following script:

select [object_id], [schema_id],
                  schema_name([schema_id]) as [schema_name],
                  [name],
                  [type],
                  [type_desc],
                  [create_date],
                  [modify_date]
from sys.all_objects
where [name]='Employee'

The result of the script will be as follows:

Img.1. The search result of the Employee table

It displays:

1. The identifiers for the object and the schema where the object is located.
2. The name of the schema and the name of the object.
3. The object type and the description.
4. The date and time of the creation and modification of the object.

To find all entries of the “Project” string, you can use the following script:

select [object_id], [schema_id],
           schema_name([schema_id]) as [schema_name], 
           [name], 
           [type], 
           [type_desc], 
           [create_date], 
           [modify_date] 
from sys.all_objects 
where [name] like '%Project%'

The result will be the output of the following table:

Img.2. The search result of the “Project” substring across all database objects

As shown in the result, the “Project” string is not only contained in the Project and ProjectSkill tables but in some primary and external keys.

To understand which table holds these keys, let’s add the parent_object_id field, its name, and the schema in which it is located to the output, by doing the following:

select ao.[object_id], ao.[schema_id],
          schema_name(ao.[schema_id]) as [schema_name],
          ao.parent_object_id,
          p.[schema_id] as [parent_schema_id],
          schema_name(p.[schema_id]) as [parent_schema_name],
          p.[name] as [parent_name],
          ao.[name],
          ao.[type],
          ao.[type_desc],
          ao.[create_date],
          ao.[modify_date] 
from sys.all_objects as ao 
left outer join sys.all_objects as p on ao.[parent_object_id]=p. [object_id] 
where ao.[name] like '%Project%'

The result will be the table output with the detailed information on the parent objects, which means, where the primary and external keys are specified:

Img.3. The search result of the “Project” substring across all database objects with the parent object details.

The following system objects are used in the queries:

1. The sys.all_objects table.
2. The schema_name scalar-valued function.

You can find the string value in all database tables by means of this solution. Let’s simplify this solution and show how to find for instance the value “Ramiro” with the following script:

The output may be the following:

Img.4. The database search result

Here, the output shows the names of the tables and which columns store the value that contains the substring “Ramiro” as well as the number of found outputs of this substring for the found match table-column.

To find the objects whose definitions contain given code fragments, you can use the following system views:

1. sys.sql_modules
2. sys.all_sql_modules
3. sys.syscomments

For instance, using the last view, you can find all objects whose definitions contain the given code fragment with the help of the following script:

select obj.[object_id], 
            obj.[name], 
            obj.[type_desc], 
            sc.[text] 
from sys.syscomments as sc 
inner join sys.objects obj on sc.[id]=obj.[object_id] 
where sc.[text] like '%code snippet%'

Here, the output shows the identifier, the name, the description, and the full definition of the object.

Searching for database data and objects using dbForge Search

It is far more convenient to search for data and objects with the help of ready-made handy tools. One of such tools is dbForge Search.

To call up this tool, press dbForge Search in the SSMS window. The search window appears:

Img. 5 The search window of dbForge Search

Pay your attention to the top panel (from left to right), as you can change:

• The search mode (search for DDL (objects) or data).
• What we actually search for (which substring).
• Case sensitivity, search for the exact match for a word, or search for string entries.

Img.6. Search modes

You can also:

• Group the results by object type with the top center button (two squares joined by a curly brace).
• Select the necessary object types for the search:

Img.7. Selecting object types for the search

• Set up several databases for the search and select an MS SQL Server instance

This is the object search mode, that is when DDL is included:

Img.8. Search by DDL objects

In the data search mode, the only different thing is the object type selection:

Img.9. Search by data

That is to say, only the tables where the data is stored are available for selection:

Img.10. Selecting tables for data search

Now, in the same way as before, let us find all “Project” substring entries in the object names:

Img.11. The search result of all “Project” string entries in the database object names

Apparently, the search mode was set by DDL objects, we are looking for the “Project” string, so it is filled, everything else was left at default.

When selecting the retrieved object, you can see the definition code of the given object and its parent object below.

Also, you can shift the navigation to the retrieved object by pressing the button shown below:

Img.12. Shifting the navigation to the retrieved object

You can also group the found objects by their type:

Img.13. The result of the search conducted by objects with grouping by the type

Note that even the tables than contain the fields whose names include the “Project” substring are displayed. However, let me remind you that the search mode can be changed: search for the whole match or partial match, case sensitive or not.

Now, let us find the “Ramiro” value across all tables:

Img.14. The search result of the “Ramiro” substring across all database data

Note that all the strings containing the “Ramiro” substring on the selected Employee table are displayed.

Also, you can shift navigation to the found object by pressing the button shown below just like we did before:

Img.15. Shifting navigation to the found object

As a result, we can search for the desired objects and data in the database.

Conclusion

Well, in a nutshell, we came a long way from the idea to the implementation of the database for a recruitment service. Let’s summarize what we have achieved so far:

That’s all, the database is ready for use, and after the tests are run, it can be integrated, and it will exist. So what’s next? Later, we will need to maintain the database and introduce changes whenever required.

Read also:

1. SQL Database Design Basics with Example
2. Populating the Employee Database with Test Data
3. Exporting and Importing JSON Data

Originally published at https://blog.devart.com on August 11, 2020.