|
|
Posted by Erland Sommarskog on 09/20/07 21:31
(krusz10@hotmail.co.uk) writes:
> I have a table (tableA) that lists a number of other tables on the
> same database. I want to search each table listed in tableA for an
> existence of an known entry and then display that entry and the table
> its in
> I can do it in other scripting languages but I'm new to SQL. Can you
> help
>
> I sort of want to do the following
> Select * from everything_in(select tablename from tableA) where entry
>= 'fred'
Sounds like you have a bad database design. Normally, each table should
describe a unique entity, why a query like your would not be meaningful
in most cases. (The one exception I can think of is auditing columns that
could appear in all tables.)
The query to write is:
WITH all_tables AS (
SELECT ...
FROM tblA
UNION ALL
SELECT ...
FROM tblB
...
)
SELECT ... FROM all_tables WHERE col = 'fred'
If you want to work from yor table of table names, you would need to
generate the query dynamically.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|