UmbraCare LOGO

How to Examine Content Property Values for Given Data Type in Umbraco

Discover how to review content property values in Umbraco CMS v11 using T-SQL. It is tailored for developers looking to optimize content management, assess data type usage, and ensure smooth maintenance.

Published on: , by:
Piotr Bach
Piotr Bach
Wojciech Tengler
Wojciech Tengler
Umbraco MS SQL

Table of contents

Introduction

As Umbraco continues to evolve as a flexible and powerful Content Management System (CMS), sometimes you need to delve into the specifics of content property values.

Understanding how these values interact with various data types is crucial for maintenance, optimization, or just a routine check.

This article provides insights on using a T-SQL to examine content property values for a given data type in Umbraco, offering a valuable tool in multiple scenarios.

Find the Data Type ID in the Back-office

Before running the script, you must find the data type ID in the Umbraco back office.

The easiest way is to navigate to Sections > Settings > Data Types

Then, you need to copy the datatype from the browser URL.

 /umbraco/#/settings/dataTypes/edit/{dataTypeId}.

Replace @dataTypeId in the script with the actual ID of the data type you wish to examine.

Let's assume we are looking for Blog Article Tags represented by Multinode Treepicker.

Finding Multinode Treepicker data type identifier in Umbraco Backoffice

Finding Multinode Treepicker data type identifier in Umbraco Backoffice

T-SQL Query

DECLARE @dataTypeId INT = 1126;

/* Review Umbraco property data for data type across all content types */
SELECT  
PT.Id AS PropertyTypeId,
PT.Alias AS PropertyTypeAlias, 
CT.Alias AS ContentTypeAlias,
PD.textValue AS TextValue
FROM [dbo].cmsPropertyType PT
JOIN [dbo].[cmsContentType] CT ON CT.nodeId = PT.contentTypeId
JOIN [dbo].[umbracoPropertyData] PD ON PD.propertyTypeId = PT.Id
JOIN [dbo].umbracoContentVersion CV ON PD.versionId = CV.id
JOIN [dbo].umbracoDocumentVersion DV ON DV.id = CV.id
WHERE dataTypeId = @dataTypeId AND DV.published = 1 -- only for published content

Query results

TSQL Query Results for Umbraco Multinode Treepicker values

TSQL Query Results for Umbraco Multinode Treepicker values

Key script insights

The script's primary goal is to gather information about the specific types of properties used in the content (like text fields, date pickers, etc.), what content types they are part of, and the data stored in these properties.

However, notice that we only fetch content that is currently published.

To obtain all the above information, we need to join five tables:

  • cmsPropertyType (PT): Holds definitions of property types in Umbraco. These are the types of properties that can be assigned to content, like text fields, image pickers, etc.
  • cmsContentType (CT): Contains definitions of content types in Umbraco. Content types define the structure of different content nodes, like articles, product pages, etc.
  • umbracoPropertyData (PD): Stores the actual data entered into the properties described in cmsPropertyType for each content node.
  • umbracoContentVersion (CV) and umbracoDocumentVersion (DV): These tables manage different versions of content, including whether a version is published or not.

Common development and maintenance scenarios

Here are some development and maintenance scenarios where you need such a query:

  1. Content Auditing for Compliance: In scenarios where your content needs to adhere to specific regulations or standards, you can review all published content for compliance with such standards. By examining the current values, you can ensure that your content meets legal, SEO, or brand guidelines.

  2. Analyzing Content for Optimization: You can analyze content for optimization purposes. By reviewing how certain data types are used across different content, you can identify trends, inconsistencies, or opportunities for improving content quality, structure, or SEO performance.

  3. Preparing for Content Migration or Redesign: Before undertaking a major site redesign or content migration, it's essential to understand the landscape of your existing content to ensure a smooth transition.

Conclusion

Understanding and managing content property values are critical to developing and maintaining an efficient Umbraco system. 

The script we've discussed is a helpful asset, addressing various everyday development needs.

🌐 Explore More: Interested in learning about Umbraco and other web development insights? Explore our blog for a wealth of information and expert advice.

Related articles

↑ Top ↑