Find all column names that are primary keys in Firebird database

Question

I need to find all columns in a table that are primary keys and return their column names.

Is there a way I can achieve this with the Connection.GetSchema() method of the Firebird .net API? If not, how would a SQL statement look like to achieve this?

I don't need a complete code example but some guidance on how to do it.

In my scenario I have a Firebird embedded database and I want to generate stored prodecures from the database schema. I need the primary key column names to generate triggers on a table that insert some metadata into a tracking table.

I use the latest version of Firebird with the latest .net connector (v.2.5.5)

If you need more information about my problem please comment!

Answer 1

To select primary key fields of all tables you can use Firebird system tables like :

select
    ix.rdb$index_name as index_name,
    sg.rdb$field_name as field_name,
    rc.rdb$relation_name as table_name
from
    rdb$indices ix
    left join rdb$index_segments sg on ix.rdb$index_name = sg.rdb$index_name
    left join rdb$relation_constraints rc on rc.rdb$index_name = ix.rdb$index_name
where
    rc.rdb$constraint_type = 'PRIMARY KEY'