Description of the general need for finding data and objects in a database
The search for data, as well as stored procedures, tables, and other objects in the database is a rather relevant issue, including for C # developers, as well as for .NET development in general.
Quite often, a situation may arise in which you need to find:
- database object (table, view, stored procedure, function, etc.)
- data (value and in which table is located)
- code snippet in database object definitions
There are many ready-made solutions, both paid and free.
First, we will look at how you can search for data and objects in a database using the built-in DBMS tools, and then we will look at how to do this using the free dbForge Search utility.
Search using the built-in DBMS tools
You can determine if the Employee table is in the database using the following script:
Search table by name
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 could be something like this:
Here are displayed:
- identifiers of the object and the scheme where the object is located
- the name of this circuit and the name of this object
- type of object and description of this type of object
- date and time of creation and last modification of the object
To find all occurrences of the “Project” line, you can use the following script:
Search for all objects by substring in a name
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 could be something like this:
As you can see from the result, here the substring “Project” contains not only two tables Project and ProjectSkill, but also some primary and foreign keys.
To understand who exactly these keys belong to, we add the parent_object_id field and its name and the scheme in which it is located as follows:
Search for all objects by substring in the name with parent objects
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 a table with detailed information about the parent objects, i.e. where primary and foreign keys are defined:
The following system objects are used in queries:
- sys.all_objects table
- scalar function schema_name
So, we figured out how to find objects in the database using the built-in DBMS tools.
Now we show how to find data in the database using the string search example.
To find the string value for all database tables, you can use the following solution . We simplify this solution and show how you can find, for example, the value of “Ramiro” using the following script:
Search for string values ​​by substring in all database tables
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 result of the execution may be like this:
Table names are displayed here and in which columns the value containing the substring “Ramiro” is stored. As well as the number of inputs of this substring found for the found table-column pair.
To find objects in the definitions of which there is a given piece of code, you can use the following system views:
For example, using the last view, you can use the following script to find all the objects in the definitions of which a given piece of code is found:
Search for code snippet in database object definitions
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%';
The identifier, name, description and full definition of the object will be displayed here.
Search with the free dbForge Search Utility
However, it is more convenient to search using ready-made good tools. One such tool is dbForge Search .
To call this utility in the SSMS window , click on the button
.
The following search box will appear:
Pay attention to the top panel (from left to right):
- you can switch the search mode (look for DDL (objects) or data)
- directly what we are looking for (which substring)
- whether to be case sensitive, search for exact word matching, search for occurrences:
- group result by object type - button
- select the desired types of objects to search:
- You can also specify several databases to search and select an instance of MS SQL Server
This is all in object search mode, i.e. when DDL is enabled:
In data search mode, only the selection of object types will change:
Namely, only tables will be available for selection, where the data itself is stored:
Now, as before, we will find all occurrences of the “Project” substring in the names of objects:
As you can see, the search mode for DDL objects was selected, it’s filled in that we are looking for the “Project” line, the rest was all by default.
When highlighting a found object, the definition code of this object or its entire parent object is displayed below.
You can also move the navigation to the found object by clicking on the button
:
You can also group found objects by their type:
Please note that even tables that contain fields whose names contain the substring “Project” are displayed. However, recall that the search mode can be changed: to search for full compliance / partial / case sensitive or not.
Now we find the value “Ramiro” in all the tables:
Notice that all rows that contain the “Ramiro” substring of the selected Employee table are displayed below.
You can also move the navigation to the found object by pressing the button as before
:
Thus we can search for the necessary objects and data in the database.
Conclusion
Ways to search both the data itself and the objects in the database were examined both using the built-in tools of the MS SQL Server DBMS itself and using the free dbForge Search utility.
Devart also has a number of other free ready-made solutions, a complete list of which can be found here .