Dynamics 365 Business Central: How to export all keys in all tables

Hi, Readers.
Today I would like to briefly talk about how to export all keys in all tables.

In AL, a key definition is a sequence of one or more field IDs from a table. You can define keys in table objects and table extension objects, depending on the type of key. There are two types of keys: primary and secondary. More details: Table Keys

• Primary keys: A primary key uniquely identifies each record in a table. Every table has a primary key, and there can only be one primary key per table. Primary keys are defined on table objects only. In SQL, table extension objects inherit the primary key of the table object they extend (the base table object). So any key that you define in a table extension object is considered a secondary key.
• Secondary keys: Secondary keys create indexes in SQL. They’re defined in both table objects and table extension objects. You can define multiple secondary keys for a single table object and table extension object.A key in table extension object can include fields from the base table object or the table extension object. There are some limitations, however. For more information, see Limitations and Restrictions.

Keys in table 36 “Sales Header”:

When we discussed How to export all fields in all tables in Dynamics 365 Business Central, I briefly shared how to use table 2000000041 “Field” to export all fields in all tables. There is a field in this table, IsPartOfPrimaryKey, which can determine whether the field is part of the primary key.

However, you cannot export all the keys in the table using this method, nor do you know the order of the fields in the primary key. So how to do it?

This time we can use table 2000000063 “Key”.

Add “?table=2000000063” after the environment name. The “Key” table will be opened.

For example, Keys in table 36 “Sales Header”:

About SystemId field: (APPLIES TO: Business Central 2019 release wave 2 and later)
The SystemId field is a GUID data type field that specifies a unique, immutable (read-only) identifier for records in the table. The SystemId field has the following characteristics and behavior:

• All records must have a value in the SystemId field.
• You can assign your own value when a record is inserted in the database. Otherwise, the platform will automatically generate and assign a value.
• Once the SystemId has been set, it can’t be changed.
• There’s always a unique secondary key on the SystemId field to ensure records don’t have identical field values.
• The SystemId field is given the field number 2000000000.

But if you open the table directly, there is no Open in Excel action, so if you only need to view the records, this is fine.

And because you cannot use system table 2000000063 in the configuration package.

So if you need to export all keys, the easiest way is to create a list page.

In addition to the content of the keys, there are also some properties set in the key that can be found.
Very convenient, give it a try!!!😁

Source Code:

page 50103 "ZY Keys"
{
    ApplicationArea = All;
    Caption = 'Keys';
    PageType = List;
    SourceTable = "Key";
    UsageCategory = Lists;
    Editable = false;
    InsertAllowed = false;
    DeleteAllowed = false;
    ModifyAllowed = false;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(TableNo; Rec.TableNo)
                {
                    ToolTip = 'Specifies the value of the TableNo field.';
                }
                field("No."; Rec."No.")
                {
                    ToolTip = 'Specifies the value of the No. field.';
                }
                field(TableName; Rec.TableName)
                {
                    ToolTip = 'Specifies the value of the TableName field.';
                }
                field("Key"; Rec."Key")
                {
                    ToolTip = 'Specifies the value of the Key field.';
                }
                field(SumIndexFields; Rec.SumIndexFields)
                {
                    ToolTip = 'Specifies the value of the SumIndexFields field.';
                }
                field(SQLIndex; Rec.SQLIndex)
                {
                    ToolTip = 'Specifies the value of the SQLIndex field.';
                }
                field(Enabled; Rec.Enabled)
                {
                    ToolTip = 'Specifies the value of the Enabled field.';
                }
                field(MaintainSQLIndex; Rec.MaintainSQLIndex)
                {
                    ToolTip = 'Specifies the value of the MaintainSQLIndex field.';
                }
                field(MaintainSIFTIndex; Rec.MaintainSIFTIndex)
                {
                    ToolTip = 'Specifies the value of the MaintainSIFTIndex field.';
                }
                field(Clustered; Rec.Clustered)
                {
                    ToolTip = 'Specifies the value of the Clustered field.';
                }
                field(ObsoleteState; Rec.ObsoleteState)
                {
                    ToolTip = 'Specifies the value of the ObsoleteState field.';
                }
                field(ObsoleteReason; Rec.ObsoleteReason)
                {
                    ToolTip = 'Specifies the value of the ObsoleteReason field.';
                }
                field(Unique; Rec.Unique)
                {
                    ToolTip = 'Specifies the value of the Unique field.';
                }
            }
        }
    }
}

PS:
1. How to get fields in the keys of the table (Using KeyRef Data Type)

2. Creating new keys in a tableextension with fields from the base table

3. Total number of keys: Up to 40 keys can be associated with a table.

4. Key modifications: When developing a new version of an extension, be aware of the following restrictions to avoid schema synchronization errors that prevent you from publishing the new version:

• Don’t delete primary keys.
• Don’t add or remove primary key fields, nor change their order.
• Don’t change properties of existing primary keys.
• Don’t add more unique keys.
• Don’t add more clustered keys.
• Don’t add keys that are fields of the base table.

END

Hope this will help.

Thanks for reading.

ZHU