CODEWITHSUNDEEP
sql-server
NP007
NP007

Reputation: 688

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

As per my requirement, I have to find if some words like [email protected] 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.

Upvotes: 4

Views: 121

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

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.

Upvotes: 2

JERRY
JERRY

Reputation: 1173

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.

Upvotes: 1

Related Questions