community.borland.com

Article #25259: Retrieving Information about InterBase System Objects

Problem:
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;







Last Modified: 02-OCT-00