How to Search for Database Objects and Table Data in SQL Server

August 11th, 2020

Hello and welcome to the fourth part of our 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:

The result shown after running the script

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:

The table displaying the result of the search of the Project substring

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:

The ouput that shows detailed information on the parent objects

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:

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s

The output may be the following:

The search result in the SQL database

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:

The search window of the SQL search tool

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.
The modes used to set up the search

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:
Selecting the types of objects to be searched in SQL database

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:

The search mode by DDL objects

Img.8. Search by DDL objects

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

The search mode by data

Img.9. Search by data

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

Selecting the tables where we want to search data

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:

The result of the search for all “Project” substring entries

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:

The button you should use to shift the navigation to the found object
The navigation is shifted to the retrieved object

Img.12. Shifting the navigation to the retrieved object

You can also group the found objects by their type:

The search result with grouping by the type of objects

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:

The navigaton is shifted to the found object

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.

Only one more aspect is yet to cover, the occurrence of invalid objects and the ways to find invalid objects in the SQL Server database.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment