Searching for a value across multiple tables

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  November 11, 2015 at 3:16 am

  #322150

  CREATE TABLE #tblContacts

  (

  ContactID int,

  Contact varchar(50)

  )

  GO

  INSERT INTO #tblContacts(ContactID, Contact)

  SELECT 1,'Fred' UNION ALL

  SELECT 2,'Jim' UNION ALL

  SELECT 3,'Jack'

  CREATE TABLE #tblProjects1

  (

  ProjectID1 int,

  ContactID int

  )

  GO

  INSERT INTO #tblProjects1(ProjectID1, ContactID)

  SELECT 1,1 UNION ALL

  SELECT 2,3

  CREATE TABLE #tblProjects2

  (

  ProjectID2 int,

  ContactID int

  )

  GO

  INSERT INTO #tblProjects2(ProjectID2, ContactID)

  SELECT 1,1 UNION ALL

  SELECT 2,2

  SELECT * FROM #tblContacts

  INNER JOIN #tblProjects1 ON #tblContacts.ContactID = #tblProjects1.ContactID

  SELECT * FROM #tblContacts

  INNER JOIN #tblProjects2 ON #tblContacts.ContactID = #tblProjects2.ContactID

  DROP TABLE #tblContacts

  DROP TABLE #tblProjects1

  DROP TABLE #tblProjects2

  I have a database with tblContacts containing a list of people.

  The database also contains 804 other tables - many of which have a ContactID column.

  In my example code above, I want to determine which tables each contact has a record in.

  So, in English, I want a result set that looks like this.

  Fred .... Fred appears in tblProjects1, tblProjects2

  Jim ... Jim appears in tblProjects2

  Jack ... Jack appears in tblProjects1

  So, having got a list of Contacts (Select * FROM #tblContacts) - I need to loop through every table in the database and determine which table each person in my list of contacts has a record(s) in.

  I have no idea how to do this or whether it can be done. Any help much appreciated.

• lnardozi 61862

  SSCommitted

  Points: 1931

  More actions

  November 11, 2015 at 5:03 am

  #1839064

  first, try this - you'll need to run it in each database

  select * from information_schema.columns where column_name in ('contact_id','contactid',etc.)

  That'll give you the names of the tables. If they never change, you could just make a view

  create view my_view as

  select 'table1' as tablename, * from table1

  union all

  select 'table2', * from table2

  and so on

  if they DO change often, the same original select can be used with a cursor to create a dynamic sql.

• Ant-Green

  SSC Guru

  Points: 113512

  More actions

  November 11, 2015 at 5:16 am

  #1839067

  Something like this http://www.sqlservercentral.com/scripts/String/89388/