Select Git revision
clear_schema_ddl.sql
Code owners
Assign users and groups as approvers for specific file changes. 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
;