Tech Blog :: Drupal 7 / Drush tip: Find all field content using a text format

Aug 2 '11 10:51am

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''";

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:

  1. Find all the tables used for content storage.
  2. Find all the 'format' fields in those tables. (They'll only exist if the field uses formats.)
  3. 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.

Posting a comment which David Rothstein tried to post (but couldn't because of some error with the spam filter on here):
Note that the above script will find all fields that use the text format, but not all content that does. For example, custom blocks, user signatures, and who-knows-what-else in contrib modules all have text formats which are stored outside the field API, so if you are using any of those you'd need to check a bunch of other database tables also.
Very good point.