达梦数据库-批量删除对象sql

–级联删除OA模式下所有的表

select
        'DROP TABLE OA.'
        || a .table_name
        ||' cascade;'
from
        dba_tables a
where
        owner='OA';

–删除OA模式下所有的视图

select
        'DROP VIEW OA.'
        || a.view_name
        ||';'
from
        dba_views a
where
        owner='OA';

–删除OA模式下所有的序列

select
        'DROP SEQUENCE OA."'
        ||a.sequence_name
        ||'";'
from
        SYS.DBA_SEQUENCES a
where
        A.SEQUENCE_owner='OA';

–删除OA模式下所有的存储过程

select
        'DROP PROCEDURE OA."'
        ||NAME
        ||'";'
from
        sysobjects
where
        SYSOBJECTS."SUBTYPE$"='PROC'
    AND SCHID               in
        (
                select id from sysobjects where type$='SCH' AND NAME='OA'
        );

–删除OA模式下所有的触发器

select
        'DROP TRIGGER OA."'
        ||a.trigger_name
        ||'";'
from
        dba_triggers a
WHERE
        owner='OA';

–删除OA模式下所有包

select
        'DROP PACKAGE OA."'
        || name
        ||'";'
from
        sysobjects
where
        SYSOBJECTS."SUBTYPE$"='PKG'
    AND SCHID               IN
        (
                SELECT ID FROM sysobjects where type$='SCH' AND NAME='OA'
        );