What is easiest and optimize way to find specific value from database tables?

user_10263771 0
Viewed 96

As per my requirement, I have to find if some words like xyz@test.com value exists in which tables of columns. The database size is very huge and more than 2500 tables.

Can anyone please provide an optimal way to find this type of value from the database. I've created a loop query which took around almost more than 9 hrs to run.

sql-server
2 Answers

9 hours is clearly a long time. Furthermore, 2,500 tables seems close to insanity for me.

Here is one approach that will run 1 query per table, not one per column. Now I have no idea how this will perform against 2,500 tables. I suspect it may be horrible. That said I would strongly suggest a test filter first like Table_Name like 'OD%'

Example

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

Returns

enter image description here

If it Helps, the individual queries would look like this

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

On a side-note, you can search numeric data and even dates.

user_15700000

Make a procedure with VARCHAR datatype of column with table name and store into the temp table from system tables.

Now make one dynamic Query with executing a LOOP on each record with = condition with input parameter of email address.

If condition is matched in any statement using IF EXISTS statement, then store that table name and column name in another temp table. and retrieve the list of those records from temp table at end of the execution.

user_53364390
Related