Drupal 7 / Drush tip: Find all field content using a text format
I'm working on a Drupal 7 site and decided one of the text formats ("input formats" in D6) was redundant. So I disabled it, and was warned that "any content stored with that format will not be displayed." How do I know what content is using that format? This little shell snippet told me:
drush sql-query "show tables like 'field_data_%'" | tail -n+2 | while read TABLE; do
FIELD=`drush sql-query "show fields in $TABLE like '%format%';" | tail -n+2 | awk '{ print $1 }'`;
echo "$TABLE - $FIELD";
if [[ "$FIELD" != "" ]]; then
drush sql-query "select * from ${TABLE} where ${FIELD}='old_format''";
fi
done
You'll need to run that in the terminal from your site's webroot and have Drush installed. Rename old_format
to the code name of your text format. (drush sql-query "select * from {filter_format}"
will show you that.) It'll work as a single command if you copy and paste it (as multiple lines or with line breaks stripped - the semi-colons indicate the end of each statement).
Breaking it down:
- Find all the tables used for content storage.
- Find all the 'format' fields in those tables. (They'll only exist if the field uses formats.)
- Find all the rows in those tables matching the format you want to delete. Alternatively, if you want everything to be in one format, you can see what does not use that format by changing the
${FIELD}=...
condition to${FIELD}<>'new_format'
.
This won't fix anything for you, it'll just show you where to go - look at the entity_id
columns (that's the nid
if the content is nodes) and go edit that content.
Also note, this is checking the field_data_ tables, which (as far as I can tell) track the latest revision. If you are using content revisions you might want to change the first query to show tables like 'field_revision_%'
. I'm not sure why D7 duplicates so much data, but that's for another post.
Update: I modified the title from Find all content to Find all field content because of the comment by David Rothstein below.