While Sitefinity's APIs are the standard for interacting with module data, some backend scenarios (like data exports, reporting, or syncing with other systems) don't require the overhead of the full API lifecycle. For these tasks, what you really need is simple, read-only access to the raw data in a flat structure that you can quickly map into a POCO.
This stored procedure is designed for that exact purpose. It bypasses the API to provide a direct, high-performance way to query any dynamic module's data. It's a practical shortcut for when you need to get straight to the source.
The GREAT part about this is that you just tell it the module and type you want and it'll:
- Generate you a single row for each item, merging in all the data from the around the DB like scheduled task date
- Search out linked categories and tags automatically, providing the result as JSON with Taxon Ids.
Now obviously it won't do RELATED data (at least yet)... but this is a good start that works for any Sitefinity type (afaik).
I'm sure, full disclosure, Progress would** PREFER YOU USE THE API!**
Enjoy, thanks to Jonathan and Kevin Reed (no relation?)
SitefinityGetContentAsSql.sqlView on GitHub
-- Drop existing procedure if it exists
DROP PROCEDURE IF EXISTS [dbo].[sp_GetDynamicContent];
GO
CREATE PROCEDURE [dbo].[sp_GetDynamicContent]
@ModuleName NVARCHAR(100),
@TypeName NVARCHAR(100)
AS
BEGIN
/*
================================================================================
GENERIC SITEFINITY DYNAMIC CONTENT QUERY
================================================================================
This stored procedure dynamically queries any Sitefinity dynamic module type
by constructing the table name from module and type names.
PARAMETERS:
-----------
@ModuleName: The module name (e.g., 'ExampleModule', 'CustomModule', 'MyModule')
@TypeName: The content type name (e.g., 'ExampleType', 'CustomItem', 'MyContent')
TABLE NAMING CONVENTION:
------------------------
Sitefinity creates tables as: {module}_{type} (all lowercase)
Example: 'ExampleModule' + 'ExampleType' = 'examplemodule_exampletype'
APPROACH:
---------
1. Dynamically constructs the module table name
2. Joins sf_dynamic_content with the module table
3. Returns ALL columns from both tables merged into single rows
4. Includes publication state calculation
5. Automatically detects and includes categories/tags as JSON if tables exist
USAGE:
------
EXEC sp_GetDynamicContent 'ExampleModule', 'ExampleType';
EXEC sp_GetDynamicContent 'CustomModule', 'CustomItem';
================================================================================
*/
SET NOCOUNT ON;
DECLARE @TableName NVARCHAR(255);
DECLARE @CategoryTableName NVARCHAR(255);
DECLARE @TagsTableName NVARCHAR(255);
DECLARE @HasCategories BIT = 0;
DECLARE @HasTags BIT = 0;
DECLARE @SQL NVARCHAR(MAX);
-- Construct the table names (lowercase module_type pattern)
SET @TableName = LOWER(@ModuleName) + '_' + LOWER(@TypeName);
SET @CategoryTableName = @TableName + '_category';
SET @TagsTableName = @TableName + '_tags';
-- Verify the main table exists
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = 'dbo')
BEGIN
RAISERROR('Table %s does not exist in the database.', 16, 1, @TableName);
RETURN;
END
-- Check if category table exists
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @CategoryTableName AND TABLE_SCHEMA = 'dbo')
BEGIN
SET @HasCategories = 1;
END
-- Check if tags table exists
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TagsTableName AND TABLE_SCHEMA = 'dbo')
BEGIN
SET @HasTags = 1;
END
-- Build dynamic SQL to query all columns from both tables
SET @SQL = N'
WITH
-- Pre-calculate publication states
PublishedItems AS (
SELECT DISTINCT original_content_id AS master_id
FROM sf_dynamic_content
WHERE status = 2 -- Live
AND visible = 1 -- Must be visible to be truly published
),
ScheduledItems AS (
SELECT DISTINCT
TRY_CAST(SUBSTRING(task_data,
PATINDEX(''%ContentItemMasterId="[0-9a-fA-F]%'', task_data) + 21,
36) AS UNIQUEIDENTIFIER) AS master_id,
execute_time AS scheduled_date
FROM sf_scheduled_tasks
WHERE enabled = 1
AND execute_time > GETDATE()
AND task_data LIKE ''%ContentItemMasterId=%''
AND PATINDEX(''%ContentItemMasterId="[0-9a-fA-F]%'', task_data) > 0
)
SELECT
-- All columns from sf_dynamic_content
dc.base_id,
dc.publication_date,
dc.expiration_date,
dc.last_modified,
dc.date_created,
dc.last_modified_by,
dc.system_parent_id,
dc.original_content_id,
-- Calculated publication state columns
CASE
WHEN p.master_id IS NOT NULL THEN ''Published''
WHEN s.master_id IS NOT NULL THEN ''Scheduled''
WHEN dc.status = 4 THEN ''Deleted''
ELSE ''Unpublished''
END AS sf_publication_state,
CASE
WHEN p.master_id IS NOT NULL THEN ''Published''
WHEN s.master_id IS NOT NULL THEN ''Scheduled''
WHEN dc.status = 4 THEN ''Deleted''
ELSE ''Unpublished''
END AS calculated_state,
s.scheduled_date AS scheduled_publish_date,
-- All columns from the module-specific table
mt.*' +
-- Add categories column if table exists
CASE WHEN @HasCategories = 1 THEN N',
-- Categories as JSON
(
SELECT
t.id,
t.title_ as title,
t.url_name_ as url_name
FROM ' + QUOTENAME(@CategoryTableName) + N' c
INNER JOIN sf_taxa t ON c.val = t.id
WHERE c.base_id = dc.base_id
ORDER BY c.seq
FOR JSON PATH
) AS categories'
ELSE N''
END +
-- Add tags column if table exists
CASE WHEN @HasTags = 1 THEN N',
-- Tags as JSON
(
SELECT
t.id,
t.title_ as title,
t.url_name_ as url_name
FROM ' + QUOTENAME(@TagsTableName) + N' tg
INNER JOIN sf_taxa t ON tg.val = t.id
WHERE tg.base_id = dc.base_id
ORDER BY tg.seq
FOR JSON PATH
) AS tags'
ELSE N''
END + N'
FROM sf_dynamic_content dc
INNER JOIN ' + QUOTENAME(@TableName) + N' mt ON dc.base_id = mt.base_id
LEFT JOIN PublishedItems p ON dc.base_id = p.master_id
LEFT JOIN ScheduledItems s ON dc.base_id = s.master_id AND s.master_id IS NOT NULL
WHERE dc.status = 0 -- Master records only
ORDER BY dc.last_modified DESC;';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;
END
GO
/*
================================================================================
USAGE EXAMPLES
================================================================================
-- Query all records from ExampleModule with ExampleType:
EXEC sp_GetDynamicContent 'ExampleModule', 'ExampleType';
-- Query all records from a CustomModule:
EXEC sp_GetDynamicContent 'CustomModule', 'CustomItem';
-- Query records from MyModule:
EXEC sp_GetDynamicContent 'MyModule', 'MyContent';
-- Query parent and child types separately:
EXEC sp_GetDynamicContent 'ParentModule', 'ParentType';
EXEC sp_GetDynamicContent 'ParentModule', 'ChildType';
NOTES:
------
1. The procedure automatically merges all columns from both tables
2. Module-specific columns are prefixed with 'mt.*' in the output
3. Publication state is calculated based on Live records and scheduled tasks
4. Only Master records (status=0) are returned to avoid duplicates
5. Results are ordered by last_modified descending (most recent first)
6. Categories and tags are automatically included as JSON columns if the tables exist
- Table pattern: {module}_{type}_category and {module}_{type}_tags
- JSON includes: id, title, url_name from sf_taxa
================================================================================
*/