在sql server中搜索数据库对象和表数据

最新推荐文章于 2024-05-07 10:26:13 发布

weixin_26704853

最新推荐文章于 2024-05-07 10:26:13 发布

阅读量410

点赞数

文章标签：数据库 python java mysql sql

原文链接：https://towardsdatascience.com/searching-for-database-objects-and-table-data-in-sql-server-f42f7e3e76a1

版权

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.

您好，欢迎阅读我的系列文章的第四部分，该文章将帮助您发现如何设计和使用数据库。这次，我们将深入研究SQL Server中复杂的数据和对象搜索问题，将首先在标准脚本的帮助下进一步进行此操作，然后再使用dbForge Search工具进行进一步的操作。

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

当我们需要找到以下信息时，我们经常会遇到这种情况：

A database object (a table, a view, a stored procedure, a function, etc.).
数据库对象(表，视图，存储过程，函数等)。
Data (value and the table that contains it).
数据(值和包含它的表)。
A code fragment in the database object definitions.
数据库对象定义中的代码片段。

使用标准脚本在SQL Server中进行数据和对象搜索 (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:

首先让我们展示如何在标准脚本的帮助下执行此操作。您可以使用以下脚本在数据库中搜索Employee表：

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:

该脚本的结果如下：

Image for post — Img.1. The search result of the Employee table

It displays:

它显示：

The identifiers for the object and the schema where the object is located.
对象的标识符以及对象所在的架构。
The name of the schema and the name of the object.
模式的名称和对象的名称。
The object type and the description.
对象类型和描述。
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:

结果将是下表的输出：

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.

如结果所示，“ Project”字符串不仅包含在Project和ProjectSkill表中，而且还包含在一些主键和外部键中。

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:

要了解哪个表包含这些键，让我们通过执行以下操作将parent_object_id字段，其名称以及它所在的模式添加到输出中：

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 following system objects are used in the queries:

在查询中使用以下系统对象：

The sys.all_objects table.
sys.all_objects表。
The schema_name scalar-valued function.
schema_name标量值函数。

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:

通过此解决方案，您可以在所有数据库表中找到字符串值。让我们简化此解决方案，并显示如何使用以下脚本查找值“ Ramiro”：

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:

输出可能如下：

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.

此处，输出显示表的名称，哪些列存储包含子字符串“ Ramiro”的值，以及找到的匹配表列的该子字符串的找到的输出数。

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

要查找其定义包含给定代码片段的对象，可以使用以下系统视图：

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.

在此，输出显示对象的标识符，名称，描述和完整定义。

使用dbForge搜索来搜索数据库数据和对象 (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.

在现成的方便工具的帮助下搜索数据和对象要方便得多。 dbForge Search就是这样的工具之一。

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

要调用此工具，请在SSMS窗口中按dbForge搜索。搜索窗口出现：

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

您可以注意顶部面板(从左到右)，因为您可以更改：

The search mode (search for DDL (objects) or data).
搜索模式(搜索DDL(对象)或数据)。
What we actually search for (which substring).
我们实际搜索的内容(哪个子字符串)。
Case sensitivity, search for the exact match for a word, or search for string entries.
区分大小写，搜索单词的完全匹配或搜索字符串条目。

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:
选择搜索所需的对象类型：

Set up several databases for the search and select an MS SQL Server instance
设置多个数据库进行搜索，然后选择一个MS SQL Server实例

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

这是对象搜索模式，即包含DDL时：

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

在数据搜索模式下，唯一不同的是对象类型选择：

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

也就是说，只有存储数据的表可供选择：

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

现在，以与以前相同的方式，让我们在对象名称中找到所有“ Project”子字符串条目：

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.

显然，搜索模式是由DDL对象设置的，我们正在寻找“ Project”字符串，因此已将其填充，其他所有内容均保留为默认设置。

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:

另外，您可以通过按如下所示的按钮将导航切换到检索到的对象：

You can also group the found objects by their 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.

请注意，甚至会显示包含名称中包含“ Project”子字符串的字段的表。但是，让我提醒您，可以更改搜索模式：搜索全部匹配或部分匹配，区分大小写。

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

现在，让我们在所有表中查找“ Ramiro”值：

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

请注意，将显示所选Employee表上所有包含“ Ramiro”子字符串的字符串。

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

同样，您可以像以前一样通过按下下面所示的按钮将导航切换到找到的对象：

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.

就是说，数据库已准备就绪，可以使用，并且在运行测试之后，可以对其进行集成，并且该数据库将存在。下一个是什么？稍后，我们将需要维护数据库并在需要时进行更改。