sql-server - 从数据库表中查找特定值的最简单且优化的方法是什么？

Question

根据我的要求，我必须查找诸如 xyz@test.com value 之类的单词是否存在于哪些列的表格中。数据库规模非常庞大，有2500多个表。

任何人都可以提供从数据库中查找此类值的最佳方法吗？我创建了一个循环查询，运行时间几乎超过 9 个小时。

Answer 1

9个小时显然是一个很长的时间。此外，2,500 张 table 对我来说似乎近乎疯狂。

这是一种方法，每个表运行 1 个查询，而不是每列运行一个查询。现在我不知道这对 2,500 个表的性能如何。我怀疑这可能很可怕。也就是说，我强烈建议首先使用一个测试过滤器，例如 Table_Name like 'OD%'

示例

Declare @Search varchar(max) = 'cappelletti'  -- Exact match '"cappelletti"'

Create Table #Temp (TableName varchar(500),RecordData xml)
Declare @SQL varchar(max) = ''
Select @SQL = @SQL+ ';Insert Into #Temp Select TableName='''+concat(quotename(Table_Schema),'.',quotename(table_name))+''',RecordData = (Select A.* for XML RAW) From '+concat(quotename(Table_Schema),'.',quotename(table_name))+' A Where (Select A.* for XML RAW) like ''%'+@Search+'%'''+char(10)
 From  INFORMATION_SCHEMA.Tables 
 Where Table_Type ='BASE TABLE'
   and Table_Name like 'OD%'    -- **** Would REALLY Recommend a REASONABLE Filter *** --

Exec(@SQL)

Select A.TableName
      ,B.*
      ,A.RecordData
 From  #Temp A
 Cross Apply (
                Select ColumnName = a.value('local-name(.)','varchar(100)')
                      ,Value      = a.value('.','varchar(max)') 
                 From  A.RecordData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('.','varchar(max)')  Like '%'+@Search+'%'
             ) B


Drop Table #Temp

返回

如果有帮助，各个查询将如下所示

Select TableName='[dbo].[OD]'
      ,RecordData= (Select A.* for XML RAW) 
 From  [dbo].[OD] A 
 Where (Select A.* for XML RAW) like '%cappelletti%'

附注，您可以搜索数字数据甚至日期。