⚙ Database Table Locations

Item

[lookup.query]

[lookup.requirement]

Highly complex; filters stored in [lookup.query] once per form field with as many operator/operand combinations as needed in [lookup.requirement].

See Find Forms Using Old STL Conditional Logic.

SELECT *
FROM [lookup.requirement]
WHERE [type] <> 'prompt'

If [group] is null, the opt-out is global. SMS is always global.

Setting the Opt-Out tag automatically inserts an entry into this table.

[message.mailing]

[message.dataset]

[lookup.origin.type]

[folder.item]

[folder]

The bases listed in “Query Bases (legacy tool)” can by found with WHERE [source_type] IS NULL.

There are a ton of extra bases in here from forms.

The “standard” CJ bases can be found with WHERE [source_type] = 'library' AND [category] <> 'Forms'.

[query.run]

[query.run.id]

[query.run] stores each run; [query.run.id] stores each primary and secondary key (if present) returned in each run.

If [query.run.id].[identity] exceeds the limits of int, Bad Things™ will happen, so don't overuse tracking queries.

They are stored with a special key of 'portfolio'. To query on this key, use a formula. It's not available in the built-in Key filter.

Remap settings are stored as XML in the [remap] column.

Query for unmapped prompt values:

SELECT *
FROM [source.format]
WHERE remap.exist('//val[not(@dst)]') = 1

Translations use export, export2, etc. columns to store output values, whereas Content Blocks use [html] column.

These are the snippets under Inbox > Snippets.

SELECT *
FROM [message.mailing]
WHERE [folder] = 'Templates / Snippets'

[lookup.query]

[lookup.field]

Contains both CJ and STL components.

Find all legacy  Exports/Filters:

SELECT *
FROM [lookup.query] lq    
WHERE [base] IN (
        SELECT [id]
        FROM [lookup.base]
        WHERE [source_type] IS NULL
        )
    AND [source_type] IS NULL

[query]

Folder name 'SYS:Presets'

[application.bin]

Workflow bin assignments

Workflows (legacy tool) bin assignment

[workflow.bin]