0

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.

Ortund
  • 8,095
  • 18
  • 71
  • 139
  • If you have multiple tables containing an email address, your database needs to be redone. This means that you have not normalized it. – Jay Mason Jan 09 '19 at 13:37
  • 1
    Look if this would help your problem: https://stackoverflow.com/questions/38735875/is-there-any-way-to-find-a-specific-value-in-every-field-of-every-table-in-micro – iminiki Jan 09 '19 at 13:41
  • what you need my friend is [ApexSQL](https://www.apexsql.com/sql-tools-search.aspx) otherwise your gonna have to use lengthy scripts – Ulug Toprak Jan 09 '19 at 13:44
  • @JayMason I didn't design the database, I can't make changes to it either so redoing the database isn't an option now or for the foreseeable future unfortunately. – Ortund Jan 09 '19 at 13:46
  • I'd argue that having one table of Emails with something like an 'EMailID', whilst not in any way undesirable, is quite a high level of database normalization, and that it is normal to not always see that. – Cato Jan 09 '19 at 14:24
  • @Cato so this wasn't actually that complicated. The tables having the email information are the AspNetUsers table and the Users table that the developers of the database from whom I inherited the project built in to complicate matters a lot more than necessary... Still, that means only 2 records anyway – Ortund Jan 09 '19 at 14:29
  • @Ortund No, It is 2 records per user. If you have 1,000 users - thats 1,000 extra records of their email address. There is a reason its called RELATIONSHIP tables, and having the email stored in multiple places is not good practice. – Jay Mason Jan 09 '19 at 14:50

2 Answers2

2

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

select 'select * from ' + TABLE_NAME + ' where ' + COLUMN_NAME + ' = ''the@email.com''' 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH = '256'
ckal
  • 3,540
  • 1
  • 22
  • 21
  • 1
    sounds like a good idea, you can also incorporate 'TABLE_NAME' etc as output in the Select list to show the user the table/column where the value was found. – Cato Jan 09 '19 at 14:20
0

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.

Katherine Elizabeth Lightsey
  • 853
  • 5
  • 12