CODEWITHSUNDEEP
sqlsql-servert-sql
Ortund
Ortund

Reputation: 8245

How to select all records in sql database tables where any column has a particular value

I'm trying to find all the tables in my database that contain a particular email address.

The only thing I know is that this means that I'm looking for any column that is a varchar of some length.

I was thinking about some sort of loop through sys.tables and then for each table in that loop, a loop through the rows in the table and then evaluation of each column on each row.

Probably not the best way to go about it but there's things I don't know, particularly:

  1. How better to do this, and
  2. How to write this query in the first place.

Any assistance will be greatly appreciated.

Upvotes: 0

Views: 623

Answers (2)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

You're probably looking at something like sp_MSforeachtable. Find all tables with columns with [varchar] types ([nvarchar] as well right? Could they be on [char] or [nchar] columns as well?) from [sys].[columns]. Then sp_MSforeachtable to access the values in the columns.

Basically you're looking at nested cursors. One to get all "text" columns along with the associated tables. Another to run a select on each table and column.

This is brute force stuff and is not pretty.

I've been in environments with an existing (and often ancient and revered) design as well. Sometimes you just have to do stuff like this. I like writing SQL so it can be fun.

It is always nice to fantasize about building stuff from scratch to some ideal of perfection, but no plan is perfect. You have to be able to do stuff like this.

Upvotes: 0

ckal
ckal

Reputation: 3570

You could write a quick query that would build queries for each field.

select 'select * from ' + TABLE_NAME + ' where ' + COLUMN_NAME + ' = ''[email protected]''' 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH = '256'

Upvotes: 2

Related Questions