Skip to main content

TYPO3 SQL queries

Prepopulate shortcut​

When creating a prepopulate script for TYPO3, you might want to create the page tree in the TYPO3 backend and then use this SQL snippet to duplicate the pages table and remove all unecessary columns.

While this snippet provides a minimal set of columns, you might want to keep additional columns based on your specific requirements.

Examples for additional columns
Field(s)Useful...
sys_language_uid & l10n_parentfor multi-language setups
urlfor external link type pages
content_from_pidfor multi domain setups
layoutif multiple layouts are used

Drop the lines with the columns you want to keep from the ALTER TABLE statement below to keep them in the prepopulate script.

CREATE TABLE pages_copy
LIKE pages;

INSERT INTO pages_copy
SELECT *
FROM pages;

ALTER TABLE pages_copy
DROP COLUMN deleted,
DROP COLUMN hidden,
DROP COLUMN starttime,
DROP COLUMN endtime,
DROP COLUMN fe_group,
DROP COLUMN rowDescription,
DROP COLUMN editlock,
DROP COLUMN sys_language_uid,
DROP COLUMN l10n_parent,
DROP COLUMN l10n_source,
DROP COLUMN l10n_state,
DROP COLUMN l10n_diffsource,
DROP COLUMN t3ver_oid,
DROP COLUMN t3ver_wsid,
DROP COLUMN t3ver_state,
DROP COLUMN t3ver_stage,
DROP COLUMN SYS_LASTCHANGED,
DROP COLUMN shortcut,
DROP COLUMN content_from_pid,
DROP COLUMN mount_pid,
DROP COLUMN sitemap_priority,
DROP COLUMN TSconfig,
DROP COLUMN php_tree_stop,
DROP COLUMN categories,
DROP COLUMN layout,
DROP COLUMN extendToSubpages,
DROP COLUMN subtitle,
DROP COLUMN target,
DROP COLUMN url,
DROP COLUMN lastUpdated,
DROP COLUMN newUntil,
DROP COLUMN cache_timeout,
DROP COLUMN cache_tags,
DROP COLUMN shortcut_mode,
DROP COLUMN keywords,
DROP COLUMN description,
DROP COLUMN abstract,
DROP COLUMN author,
DROP COLUMN author_email,
DROP COLUMN media,
DROP COLUMN mount_pid_ol,
DROP COLUMN module,
DROP COLUMN l18n_cfg,
DROP COLUMN backend_layout,
DROP COLUMN backend_layout_next_level,
DROP COLUMN tsconfig_includes,
DROP COLUMN seo_title,
DROP COLUMN sitemap_changefreq,
DROP COLUMN canonical_link,
DROP COLUMN og_title,
DROP COLUMN og_description,
DROP COLUMN og_image,
DROP COLUMN twitter_title,
DROP COLUMN twitter_description,
DROP COLUMN twitter_image,
DROP COLUMN twitter_card;
This snippet will keep following columns:
  • uid
  • pid
  • sorting
  • perms_userid
  • perms_groupid
  • perms_user
  • perms_group
  • perms_everybody
  • doktype
  • title
  • slug
  • nav_title
  • nav_hide
  • no_search
  • is_siteroot
  • no_index
  • no_follow