Firebird删除表，触发器，索引前判断是否存在

思路

判断系统表中是否存在要删除的对象

方法

EXECUTE BLOCK + execute statement

例子：删除触发器

SET TERM ^;
EXECUTE BLOCK
AS
BEGIN
if (exists(SELECT 1 FROM RDB$TRIGGERS Where rdb$trigger_name = 'PARTS_AFTER_CHANGE')) then
execute statement 'DROP TRIGGER PARTS_AFTER_CHANGE;';
END^
SET TERM ;^

InterBase System Objects

Retrieving Information from the System tables

Objects:
--------------
* Tables/Views format
* Triggers on a table
* primary/foreign keys and table constraints
* domains
* Indices
* Procedures
* UDFs
* Generators
Solution:
Retrieving Information about a Table or View
This example uses the employee table in the employee database
in the examples directory.
------------------------------------------------------------------------------
 This select will return the field name, field type, field length, whether it 
 is nullable, and the column check constraints for a table or view
------------------------------------------------------------------------------
select r.rdb$field_name, 
    t.rdb$type_name,
    f.rdb$field_length,
    r.rdb$null_flag,
    f.rdb$validation_source 
from    rdb$relation_fields r, rdb$types t, rdb$fields f 
where   r.rdb$relation_name='EMPLOYEE' and 
    f.rdb$field_name=r.rdb$field_source and 
    t.rdb$field_name='RDB$FIELD_TYPE' and 
    f.rdb$field_type=t.rdb$type;
--------------------------------------------------
This select returns the source for a view
--------------------------------------------------
select rdb$view_source
from    rdb$relations
where   rdb$view_source=cPHONE_LISTc;
------------------------------------------------------------------------------
This select returns the primary and foreign keys for a table and the fields it 
is defined on
------------------------------------------------------------------------------
select r.rdb$constraint_type, 
    i.rdb$field_name 
from    rdb$relation_constraints r, rdb$index_segments i 
where   r.rdb$relation_name='EMPLOYEE' and 
    (r.rdb$constraint_type='PRIMARY KEY' 
        or r.rdb$constraint_type=cFOREIGN KEYc) and 
    r.rdb$index_name=i.rdb$index_name;
-------------------------------------------------------------
This select returns the check constraints on a table
-------------------------------------------------------------
select r.rdb$constraint_name, 
    r.rdb$constraint_type, 
    t.rdb$trigger_source 
from    rdb$relation_constraints r, rdb$check_constraints c, rdb$triggers t 
where   r.rdb$constraint_name=c.rdb$constraint_name and 
    c.rdb$trigger_name=t.rdb$trigger_name and 
    r.rdb$relation_name='EMPLOYEE';
------------------------------------------------------------------------------
This select returns all the triggers for a given table
------------------------------------------------------------------------------
select t.rdb$trigger_name, 
    t.rdb$trigger_sequence, 
    y.rdb$type_name, 
    t.rdb$trigger_inactive 
from    rdb$triggers t, rdb$types y 
where   t.rdb$relation_name='EMPLOYEE' and 
    t.rdb$trigger_name not like 'CHECK%' and 
    y.rdb$field_name='RDB$TRIGGER_TYPE' and 
    t.rdb$trigger_type=y.rdb$type;
==============================================================================
Retrieving Information on a Domain
------------------------------------------------------------------
This select returns the name, datatype and length, nullable, and 
check constraints on a domain
------------------------------------------------------------------
select f.rdb$field_name, 
    t.rdb$type_name, 
    f.rdb$field_length, 
    f.rdb$null_flag, 
    f.rdb$default_source, 
    f.rdb$validation_source 
from    rdb$fields f, rdb$types t 
where   f.rdb$field_name='JOBCODE' and 
    f.rdb$field_type=t.rdb$type and 
    t.rdb$field_name='RDB$FIELD_TYPE'
========================================================================
Retrieving Information on a Databasecs Indices
-----------------------------------------------------------------
This select returns the indices defined for a database 
-----------------------------------------------------------------
select i.rdb$index_name, 
    i.rdb$unique_flag, 
    i.rdb$relation_name, 
    s.rdb$field_name 
from    rdb$indices i, rdb$index_segments s 
where   i.rdb$index_name=s.rdb$index_name and 
    i.rdb$index_name not like 'RDB$%';  (exclude this from where clause if
                                                    want system indices)
========================================================================
Retrieving Information on a Procedure
-----------------------------------------------
This select returns the source for a procedure
-----------------------------------------------
select rdb$procedure_source 
from    rdb$procedures 
where   rdb$procedure_name = 'ADD_EMP_PROJ';
-------------------------------------------------------------------------
This select returns the parametersc name, datatype, datatype length, and
weather it is an input or output parameter 
-------------------------------------------------------------------------
select p.rdb$parameter_name, 
    p.rdb$parameter_type, 
    t.rdb$type_name, 
    f.rdb$field_length 
from    rdb$procedure_parameters p, rdb$fields f, rdb$types t 
where   p.rdb$field_source=f.rdb$field_name and
    p.rdb$procedure_name='ADD_EMP_PROJ' and 
    f.rdb$field_type=t.rdb$type and 
    t.rdb$field_name='RDB$FIELD_TYPE';
==============================================================================
Retrieving Information on User Defined Functions
--------------------------------------------------------------------
This select returns a functions name, module name, and entry point
--------------------------------------------------------------------
select f.rdb$function_name, 
    f.rdb$module_name, 
    f.rdb$entrypoint 
from    rdb$functions f 
where   rdb$function_name='UPPER';
-------------------------------------------------------
This select returns the returning value of a function
-------------------------------------------------------
select a.rdb$mechanism,
    t.rdb$type_name,
    a.rdb$field_length
from    rdb$function_arguments a, rdb$functions f, rdb$types t
where   f.rdb$function_name=a.rdb$function_name and
    t.rdb$field_name=cRDB$FIELD_TYPEc and
    t.rdb$type=a.rdb$field_type and
    f.rdb$function_name=cUPPERc and
    a.rdb$argument_position=f.rdb$return_argument;
--------------------------------------------------
This select returns the parameters of a function 
--------------------------------------------------
select a.rdb$argument_position,
    a.rdb$mechanism,
    t.rdb$type_name,
    a.rdb$field_length
from    rdb$function_arguments a, rdb$functions f, rdb$types t
where   f.rdb$function_name=a.rdb$function_name and
    t.rdb$field_name=cRDB$FIELD_TYPEc and
    t.rdb$type=a.rdb$field_type and
    f.rdb$function_name=cUPPERc and
    a.rdb$argument_position<>f.rdb$return_argument;
==============================================================================
Retrieving Information about Exceptions
-----------------------------------------------------------------
This select returns the exception name, exception message, and
the name and type of object that uses the exception
-----------------------------------------------------------------
select e.rdb$exception_name, 
    e.rdb$message, 
    d.rdb$dependent_name, 
    t.rdb$type_name 
from    rdb$exceptions e, rdb$dependencies d, rdb$types t 
where   e.rdb$exception_name=d.rdb$depended_on_name and
    d.rdb$dependent_type=t.rdb$type and 
    rdb$field_name='RDB$OBJECT_TYPE';
==============================================================================
Retrieving Information about Generators
---------------------------------------------
This select shows the databasecs generators
---------------------------------------------
select rdb$generator_name 
from    rdb$generators 
where   rdb$system_flag is null;`