I'm trying to find the database table where all the column config is stored that you can view through the Object Designer UI (EntitySchemaDesigner), but I don't appear to be able to find it. I have seen some of this data appears to be stored for Lookup columns in the SysEntitySchemaReference table, but for general columns I can't see it.
Is this data only stored in the MetaData column of SysSchema? Or am I just missing the table it's stored in column by column?
I need this data in order to create a SQL procedure for archiving records, as some columns which have since been removed from the Object still exist on the DB table, so I am trying to exclude such columns from the archiving process. Everything else works about the archiving procedure, the only error is when the source DB table has a column that doesn't exist in the Object's config. Any help appreciated.
Like
Alternatively, how can I get Creatio to drop columns removed through the EntitySchemaDesigner from the database safely? i.e. a cleanup of the DB tables based on what exists in the actual Creatio config.
Hi,
In Creatio, column definitions are stored in the SysSchema.MetaData table as JSON. Lookup-related metadata is additionally stored in the SysEntitySchemaReference table. However, there is no standalone "columns" table in the database.
When you delete a column using the Entity Schema Designer and publish (or perform "Clear cache and compile"), Creatio removes the column only from metadata.
The physical column in the database remains — it is not automatically dropped. This behavior is intentional to avoid accidental data loss.
If you notice leftover columns in the database, that is expected. Re-publishing or re-compiling will not remove them.
Manually deleting columns is not recommended, as it may break indexes, foreign keys, triggers, or impact future updates. But if you absolutely must:
Use the SysSchema.MetaData field to review the current columns for the entity.
Verify that there are no dependencies (constraints, triggers, views, custom code).
Use targeted ALTER TABLE ... DROP COLUMN (and DROP CONSTRAINT) statements.
Recompile the schema afterward.
Unless these orphaned columns are causing specific issues (e.g., storage, performance), it's generally best to leave them in place platform manages schema consistency based on metadata, not physical structure.