Dynamically Searching Tables

Results 1 to 2 of 2

Thread: Dynamically Searching Tables

  1. #1
    Join Date
    Dec 1969

    Default Dynamically Searching Tables

    My database has tables that are created dynamically. Is it possible to construct an SQL query so that it can search through all the tables in the database where a field equals x?<BR><BR>For example:<BR>SQL="SELECT * FROM [all the tables] WHERE fieldName=1"

  2. #2
    Join Date
    Dec 1969

    Default Yuck and double yuck

    If you really *need* to do this, then you almost surely have designed your DB wrong. You probably should be using one table instead of several.<BR><BR>The short answer is: NO, you can&#039;t do it.<BR><BR>The longer answer is: Yeah, but it&#039;s painful:<BR><BR>SELECT fld1, fld2, fld3 FROM table1 WHERE fldX=&#039;someValue&#039;<BR>UNION<BR>SELECT fldA, fldB, fldC FROM table2 WHERE fldM=&#039;someValue&#039;<BR>UNION<BR>SELECT fldX, fldY, fldZ FROM table3 WHERE fld14=&#039;someValue&#039;<BR>UNION <BR>...<BR>ORDER BY 1, 2, 3<BR><BR>(1) The field names in the various SELECTs don&#039;t have to be the same in all the tables, but the data *TYPES* of the fields have to be (or at least, depending on the DB you are using, be "conversion compatible" to the types of the fields in the first SELECT).<BR><BR>(2) You *must* SELECT the same *number* of fields in each sub-query. If one of the subqueries doesn&#039;t have a field to correspond to one of those SELECTed in the first subquery, use a constant or even NULL instead.<BR><BR>(3) The ORDER BY is optional. You can specify the ordering using positional field numbers, starting with 1 instead of zero, though.<BR><BR>So, now, you can easily build up a query that will process all the tables. If you don&#039;t know all the table names in your code, you can use ADODB.Connection.OpenSchema [see the ADO docs] to find all the table names.<BR><BR>But please do seriously consider redesigning this db!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts