Skip to content
Snippets Groups Projects
Select Git revision
  • master
  • pcre
2 results

Gemfile

Blame
  • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    This project manages its dependencies using Bundler. Learn more
    clear_schema_ddl.sql 2.23 KiB
    
    --
    -- triggers
    --
    
    SELECT 'DROP TRIGGER ' || owner || '.' || trigger_NAME || ';' || chr(10)
    FROM   ALL_TRIGGERS
    WHERE  owner = 'SYGAL_TEST'
    
    UNION ALL
    
    
    --
    -- views
    --
    
    SELECT 'DROP VIEW ' || owner || '.' || view_name || ';' || chr(10)
    FROM   all_views
    WHERE  owner      = 'SYGAL_TEST'
    
    UNION ALL
    
    
    --
    -- materialized views
    --
    
    SELECT 'DROP MATERIALIZED VIEW ' || owner || '.' || mview_name || ';' || chr(10)
    FROM   all_mviews
    WHERE  owner = 'SYGAL_TEST'
    
    UNION ALL
    
    
    --
    -- procedures and functions
    --
    
    SELECT 'DROP PROCEDURE ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
    FROM   SYS.ALL_OBJECTS
    WHERE  owner = 'SYGAL_TEST'
           and UPPER(OBJECT_TYPE) = 'PROCEDURE'
    UNION ALL
    SELECT 'DROP FUNCTION ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
    FROM   SYS.ALL_OBJECTS
    WHERE  owner = 'SYGAL_TEST'
           and UPPER(OBJECT_TYPE) = 'FUNCTION'
    
    UNION ALL
    
    
    --
    -- packages
    --
    
    SELECT 'DROP PACKAGE ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
    FROM   SYS.ALL_OBJECTS
    WHERE  owner = 'SYGAL_TEST'
           and UPPER(OBJECT_TYPE) = 'PACKAGE'
    
    UNION ALL
    
    
    --
    -- sequences
    --
    
    SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';' || chr(10)
    FROM all_sequences
    where sequence_owner = 'SYGAL_TEST'
    
    UNION ALL
    
    
    --
    -- indexes : supprimés avec les constraints ci-dessous
    --
    
    -- SELECT 'DROP INDEX ' || owner || '.' || index_name || ';' || chr(10)
    -- FROM   all_indexes
    -- WHERE  owner = 'SYGAL_TEST'
    --
    -- UNION ALL
    
    
    --
    -- constraints
    --
    
    -- d'abord les FK
    select 'ALTER TABLE ' || OWNER || '.' || table_name || ' drop CONSTRAINT ' || constraint_name || ';' || chr(10)
    from all_constraints
    where constraint_type = 'R'
          and owner = 'SYGAL_TEST'
    union all
    -- ensuite les PK
    select 'ALTER TABLE ' || OWNER || '.' || table_name || ' drop CONSTRAINT ' || constraint_name || ';' || chr(10)
    from all_constraints
    where constraint_type = 'P'
          and owner = 'SYGAL_TEST'
    
    UNION ALL
    
    
    --
    -- tables
    --
    
    SELECT 'DROP TABLE ' || owner || '.' || table_name || ' PURGE;' || chr(10)
    FROM   all_tables
    WHERE  owner = 'SYGAL_TEST' and
           table_name not in (select mview_name from all_mviews) -- exclusion des tables liées à des vues matérialisées
    -- UNION ALL
    -- select 'PURGE RECYCLEBIN;'
    -- FROM   all_tables
    -- WHERE  owner = 'SYGAL_TEST'
    -- and rownum = 1
    
    ;