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'
(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On 20 Sep, 22:31, Erland Sommarskog
> (krus...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
------------------------------------------------------------ ------------------------------------------------------------ ----------------------
Thanks for the prompt reply. Not sure it would help unless I could
probably use variables in some way.
The database is a back end DB to a Microsoft system (SMS). There are a
number of collection tables that perform different tasks against there
contents. Listed in the DB are unique objects that are linked by a
primary key across the tables. These unique objects can exist in any
number of the tables depending on what task is required.
What I was hoping to get is a list of tables (tasks) that a unique
object is listed in, and the list of tables I want to search in (which
change daily) are found in tblA.
Hope this makes things clearer.
Can it be done in a single query?
In simple terms a script would look something like this:
Foreach $tblname in ('select tablename from tblA')
do
If true (Select objectname from $tblname where objectname = 'FRED')
Print $tblname
(krusz10@hotmail.co.uk) writes:
> The database is a back end DB to a Microsoft system (SMS). There are a
> number of collection tables that perform different tasks against there
> contents. Listed in the DB are unique objects that are linked by a
> primary key across the tables. These unique objects can exist in any
> number of the tables depending on what task is required.
>
> What I was hoping to get is a list of tables (tasks) that a unique
> object is listed in, and the list of tables I want to search in (which
> change daily) are found in tblA.
>
> Hope this makes things clearer.
> Can it be done in a single query?
No. A query always exactly define set of tables and columns. If you don't
know which tables that you want to access, you will need to build the
query dynamically along the pattern in my first post. You could build
that query in T-SQL or in a client language, whatever your preference.
But once you know which table to query, you can query all at once if you
like. (Running a loop and querying one table at a time is not likely to
have much extra cost.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx