Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: SQL - Find rows in all tables of a Schema

    Is there a way to retrieve ALL rows in all tables of a schema?

    For example, if I'm looking for all rows with a fieldname of "app_id" within a schema name with about 40 tables:

    select *
    from schemaname.alltables
    where app_id = ?

    I want to retrieve from all 40 tables instead of manually selecting for each table within the schema.

    Please send me any examples and/or explain if this is possible.
    Thank you.

    CC

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    so, you have 40 tables in your schema. Some of them have a column called APP_ID. and you only want to select from those tbales that have this column?

    If yes? query catalog for the list of tables that has this column. use that list to build your select statement. There are many way to accomplish this. I would probably script it, let it rip and go to lunch
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Feb 2007
    Posts
    84

    All tables within this schema have the field app_id

    All of the tables within this schema have the field called "app_id". So instead of doing a select on each of the 40 tables, I would like to do it in 1 select statement, if possible.

    Thanks.

    CC

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Sure. Export all of your data to ONE .txt file. Then you can do what you are asking.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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