Multiple Table/Multiple Value Query

[loveladym]

I have DB "a" that contains a table "b" with about 1100 records that have a column "c" & I have a db "d" that contains about 100 tables that have a column "e".What I need is a select query that will cycle through the values in "e" for all tables of "d" & compare them to all values in "c" of table "b" ,to see if "e" contains(is like) a value in "c"("e" has other stuff in it too), in all the tables in "d" & return 3 fields in the table of "d" if finds a match.I also need it to do both,1)when it finds a match & 2)when it can not.I think I need a join but I would rather not if possible.I kinda inherited this screwy thing & know enough about SQL/Access to be dangerous,I tried setting up an access db with links to all the tables & executing a query but the values were not correct,any help would be appreciated.

 

[culshaja]

I would use a cursor and a temp table. And using the cursor I would add the required data to the temp table. Very processor intensive and should not be done on a frequent basis.

James James Culshaw
james@miniaturereview.co.uk

http://www.miniaturereview.co.uk

http://www.caninerescue.co.uk

 

[tlbroadbent]

You can use the undocumented system stored procedure, sp_MSforeachtable, to query all tables in a database. Try the following script. Please note that it may run for a long time if the tables in DB D are large. Performing queries with LIKE and a leading wildcard prevents SQL Server from using indexes.

Use D
Go

--Create temporary table to hold query results
Create Table #temp(Col1 <data type>, Col2 <data type>, Col3 <data type>)

--Declare variable to hold SQL statement
Declare @sql varchar(500)

--Create SQL Statement - NOTE: ? is represents a table name
Select @sql=
'If Exists (Select * From syscolumns where name=''CostCenter'' And ID=object_ID(''?''))' + char(10) +
'Exec(''Select Col1, Col2, Col3 From ? t Join a.dbo.b b On t.e Like ''''%'''' + b.c + ''''%'''')'')'
Print @sql

--Insert results of queries into temp table
Insert #temp
--Execute SQL statement for each table in current database
exec sp_msforeachtable @sql

--Select the final result set
Select * From #temp

--Cleanup
Drop table #temp Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.