November 11, 2015 at 3:16 am
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.
November 11, 2015 at 5:03 am
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.
November 11, 2015 at 5:16 am
Something like this http://www.sqlservercentral.com/scripts/String/89388/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply