Skip to main content

⚙ Database Table Locations

Where is that thing stored? 🕵️‍♂️

Item

 Table(s)
 Comments
Form Prompt Conditions and Conditional Logic

[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.
Form Merge Fields
 [form]
 Stored as XML in [form].[config].
Application Logic [lookup.requirement] 
SELECT *
FROM [lookup.requirement]
WHERE [type] <> 'prompt'
Opt-Out Information, both SMS and Email [message.optout]

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.
Inbox Snippets

[message.mailing]

 Filter on [folder] = 'Templates / Snippets'.
Deliver Recipient Lists

[message.dataset]
Origin Source Groups [lookup.origin]
Origin Source Types

[lookup.origin.type]
Origin Source Results [origin] These are the entries calculated nightly based on the origin source types.
Permissions to individual objects, like queries [access]  
Folder Location

[folder.item]

[folder]
Query Bases [lookup.base]

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'.
Portfolios
 [material]

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.
Source Format Settings
 [source.format]

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
Translation Codes and Content Blocks
 [lookup.translation]

Translations use export, export2, etc. columns to store output values, whereas Content Blocks use [html] column.
Snippets and Signatures
 [message.mailing]

These are the snippets under Inbox > Snippets.

 

SELECT *
FROM [message.mailing]
WHERE [folder] = 'Templates / Snippets'
Query Exports, Filters, etc.

[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
Shared Views

[query]

Folder name 'SYS:Presets'
Reader (legacy tool) bin assignment

[application.bin]


Workflow bin assignments

Workflows (legacy tool) bin assignment

[workflow.bin]


No Comments
Back to top
Entity Select