Issue: I have 100 tables that can have different column names for full name, date of birth, email and Id. For example: Table 1 has full name as Emp name,dob, work email and national id. Table 2 might have Full Nm, date of birth, Wrk Email, NID and Table 3 might have Full Name, birth date, Work Em, NatID and so on..for 10 or more different tables
Full Name + DOB is considered as unique column
What I need is a query or an automated way to pick those probable columns with different names and lookup those column values against a reference table which has those columns and its values (full name, date of birth, work email, national id) and if I found a match then
Desired result: I need to retrieve the table name that found a match, column name that found a match, value that matched and return "no match" if match not found
I tried using INFORMATION_SCHEMA.COLUMNS but had no luck yet. Any help would be appreciated
I think you're fine with looking at INFORMATION_SCHEMA.COLUMNS. Unless I've not followed you, you can surely do this?
select c.COLUMN_NAME, IsNull(m.Found,'No Match') Matches
from INFORMATION_SCHEMA.COLUMNS c
outer apply (
select column_name + ' is in ' + table_name Found
from INFORMATION_SCHEMA.COLUMNS x
where x.COLUMN_NAME =c.COLUMN_NAME and x.TABLE_NAME !=c.TABLE_NAME
)m
where c.table_name='MasterList'
Create your MasterList table with all the column variants you want to locate in other tables, then this will list you all the tables in the database with the columns that match.