3

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!

Wolf
  • 9,679
  • 7
  • 62
  • 108
Saladino
  • 45
  • 1
  • 6
  • *`all columns in a table that are primary keys`* -- to reduce confusion, there is only **one primary key** per table that consist of one or more columns (the latter is often called *compound primary key*) – Wolf Jan 19 '17 at 12:24

1 Answers1

11

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'
Wolf
  • 9,679
  • 7
  • 62
  • 108
Val Marinov
  • 2,705
  • 17
  • 22
  • I modified your statement to get the table name instead of the index name and it works now. Thanks for the fast answer! – Saladino Apr 14 '16 at 10:05