Steve McNiven-Scott (https://www.sitefinitysteve.com) asked a question.

Edited August 13, 2025 at 6:29 PM
Anyone have a SP\View to query dynamic content

Need to get a list in raw sql, not the API of dynamic content items and their published (or scheduled to publish) state.

 

Or even if someone at progress has a schema diagram of how it's all supposed to connect, there's no FKs in the DB to link up.

 

Just to be clear, it's just simple data, no mutlisite\multiprovider\permissions, etc... just published\unpublished\scheduled flat data. I can do the basic linking through sf_dynamic_content and the modules table, but it's the linking for publish and schedule tripping me up.

  • 6 answers
  • 122 views

  • Kevin Reed (Springthrough)

    I lurked for a bit to see if anyone had a better answer... :)

     

    sf_dynamic_content has publication date and expiration date. Expiration date is not populated in my example db, so you'd have to double check that. Use base_id to join sf_dynamic_content to your table called [modulename]_[typename]. Also note the status and visible columns in sf_dynamic_content.

     

    Find a query that will search all guid columns for a specific value. This has been helpful for me when trying to figure out database stuff. I've also had some luck asking about specific database queries in support tickets.

    • Steve McNiven-Scott (https://www.sitefinitysteve.com)

      Yeah i have all those joined up, it’s like 90% right but I need 100% lol. Like im confused by Status, Visible, Published, and then detection scheduled published eh. Not ACTUALLY “confused” but more about the combinations of them to get the states I want. Like AFAIK the scheduled published item still shows it’s state as “published” so you’d have to go to the scheduled task table to get those items to modify the data maybe?

       

      I’ve been able to get all the data items but like let’s say you have 3 in the type… published, unpublished, and scheduled published, what are the DB markers to denote those specific items.

    • Steve McNiven-Scott (https://www.sitefinitysteve.com)

      One thing they’ve always needed like ALWAYS is a flat module type. A type that stores data flat without workflow complexity. Like how form data is stored but with the module builder to create the CRUD UI.

  • Jonathan Read (Progress Software)

    For Lifecycle I always refer to this document: https://www.progress.com/documentation/sitefinity-cms/for-developers-content-lifecycle

     

    I came up with this query which seems to cover your needs and is specific to showing a schedule item matching the base_id from the dynamic content type in the sample it is pressreleases_pressrelease. execute_time is when it will be published. With some additional logic we could see if it is already published

     

    1. SELECT pr.*, dc.*, st.*
    2. FROM pressreleases_pressrelease pr
    3. JOIN sf_dynamic_content dc
    4. ON pr.base_id = dc.original_content_id
    5. JOIN sf_scheduled_tasks st
    6. ON TRY_CAST(SUBSTRING(st.task_data,
    7. PATINDEX('%ContentItemMasterId="%[0-9a-fA-F]%', st.task_data) + 21,
    8. 36) AS UNIQUEIDENTIFIER) = pr.base_id;

     

  • Steve McNiven-Scott (https://www.sitefinitysteve.com)

    @Jonathan Read (Progress Software)​ @Kevin Reed (Springthrough)​ 

     

    Thanks for the assist, here's a stored procedure that pulls in all the data merged with sf_dynamic_content and the scheduled task publish dates... along with categories and tags as JSON, and dynamically pulls in the content type fields.

     

    Doesn't pull in draft or deleted, but it's a good flat start

     

    https://www.sitefinitysteve.com/blog/direct-sql-access-to-sitefinity-dynamic-content-for-easy-poco-mapping/

    Selected as Best