Results 1 to 2 of 2

Thread: Finding tables

  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Question Unanswered: Finding tables

    Hi all,
    I need to export some tables from a huge schema. I need to export all tables that have no dependency on EMPLOYEES nor on a table that has a dependency on EMPLOYEES. I've been trying to get this to work but my problem is that I don't know how deep in the structure dependencies can occur. This is how I started

    select name from systables
    where creator = 'SYSADM' and type = 'T'
    and name <> 'EMPLOYEES'
    and
    name not in (
    select table_name from all_constraints
    where R_CONSTRAINT_NAME =
    (
    select CONSTRAINT_NAME from all_constraints where table_name = 'EMPLOYEES' ) )
    ;

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    I presume you are on oracle?

    This should work for you then

    SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER = 'SYSADM'
    AND TABLE_NAME <> 'EMPLOYEES'
    AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME =
    (
    SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES ' ) )

Posting Permissions

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