<p>Nothing native, but it can be done with an SQL query – something like this should work (note that all the queries assume that your <a href="https://docs.craftcms.com/v3/config/db-settings.html#tableprefix" rel="nofollow noreferrer"><code>dbPrefix</code></a> setting is set to <code>'craft_'</code>):</p>
<pre><code>SELECT bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NULL 
</code></pre>
<p>The above query would output a table with the <code>id</code> and <code>handle</code> for all block types that aren't used, in addition to the handle for the Matrix field they belong to, i.e.</p>
<pre><code>id  | handle             | field
------------------------------------------
13  | someMatrixBlock    | someMatrixField
4   | anotherMatrixBlock | someMatrixField
</code></pre>
<p>To actually execute the SQL query, you can use a free tool like <a href="https://www.sequelpro.com/" rel="nofollow noreferrer">Sequel Pro</a> or <a href="https://www.phpmyadmin.net/" rel="nofollow noreferrer">phpMyAdmin</a> – or <a href="http://pluginfactory.io" rel="nofollow noreferrer">write a custom plugin</a> that uses <a href="https://craftcms.com/docs/plugins/database" rel="nofollow noreferrer">the QueryBuilder</a>.</p>
<p><strong>Edit</strong></p>
<p>Upon request in the comments below, here's a query that will list all block types that <em>are</em> in use (i.e. there are blocks for each particular block type). The number of blocks for each type is listed in a <code>count</code> column:</p>
<pre><code>SELECT COUNT(bt.id) as count, bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NOT NULL 
GROUP BY bt.id    
</code></pre>
<p>...and here's a query that will output a table with all blocks, sorted by block type, with the ID and URI for the entry which &quot;owns&quot; each particular block. The following query should work for Craft 2:</p>
<pre><code>SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, i18n.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_i18n i18n on i18n.elementId = e.id
ORDER BY bt.id
</code></pre>
<p><strong>For Craft 3 (and 4)</strong>, you'll need to replace the <code>craft_elements_i18n</code> table with <code>craft_elements_sites</code>:</p>
<pre><code>SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, sites.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_sites sites on sites.elementId = e.id
ORDER BY bt.id
</code></pre>
<p><strong>If you want to exclude drafts, revisions and soft-deleted entries</strong>, include this in the query:</p>
<pre><code>INNER JOIN craft_elements els on els.id = b.ownerId 
WHERE els.dateDeleted IS NULL AND els.draftId IS NULL AND els.revisionId IS NULL AND els.archived = 0
</code></pre>
<p><strong>Edit: For Craft 4, you'll want to replace all instances of <code>b.ownerId</code> with <code>b.primaryOwnerId</code>.</strong></p>
<p>If you want to limit that last query to only list the blocks for a particular block type, you can replace the <code>ORDER BY</code> statement with this:</p>
<pre><code>WHERE bt.id = 2
</code></pre>
<p>Where <code>2</code> is the ID for the block type you want to list.</p>

CC BY-SA 3.0
2016-12-22T10:35:08.617Z
Excellent - really useful - in a similar manner then is it possible to find the number of times a block from a particular field does appear and say what page it is on
mmc501
CC BY-SA 3.0
2016-12-22T10:56:18.263Z
The number of times a block type have been used (i.e. the number of blocks with that particular block type) should be easy enough, but if you want to output the ID/handle/URL for any elements with relevant blocks you're going to have to pull multiple rows for each block type (one per element), so I don't see those two operations being very useful together.
Mats Mikkel Rummelhoff
CC BY-SA 3.0
2016-12-22T10:58:14.860Z
Thanks - the number of times of block type would be used would be very useful as we could see then what blocks we could do away with.
If there is a few then some way to find easily what entries the block is used in - even if it needed to be a separate query as there are a large number of entries on the site so could be hard to find manually
mmc501
CC BY-SA 3.0
2016-12-22T11:31:26.337Z
@mmc501 Edited my answer and added a couple queries that might prove useful.
Mats Mikkel Rummelhoff
CC BY-SA 3.0
2016-12-22T12:49:19.750Z
First class - easy to tidy up now - much appreciated!
mmc501
CC BY-SA 3.0
2017-06-07T15:17:58.247Z
@mmc501The queries you provided are SUPER helpful. Just wanted to say thank you! You helped me considerably with this answer.
dpayne
CC BY-SA 3.0
2017-06-07T15:22:05.730Z
@dpayne Awesome that it proved helpful :)
Mats Mikkel Rummelhoff
CC BY-SA 4.0
2016-12-21T17:35:25.690Z
2022-11-25T12:10:36.957Z
2022-11-25T12:10:36.957Z
Mats Mikkel Rummelhoff
<p>I have a fairly large site with the main content on most pages driven by a Matrix field. That Matrix field has 15 or so different Blocks - we are now doing a tidy up on the site and going to try and reduce the number of blocks available - is there any way to tell if a block isn't being used?</p>

<entries><matrix>
Is it possible to find what Matrix Block aren't being used
# Is it possible to find what Matrix Block aren't being used

(Asked by: username_0 on 2016-12-21)

I have a fairly large site with the main content on most pages driven by a Matrix field. That Matrix field has 15 or so different Blocks - we are now doing a tidy up on the site and going to try and reduce the number of blocks available - is there any way to tell if a block isn't being used?

**Tags:** entries, matrix

---

## 1 answers

### Answer by username_1 on 2016-12-21. Score: 11 | Accepted answer

Nothing native, but it can be done with an SQL query – something like this should work (note that all the queries assume that your dbPrefix setting is set to 'craft_'):
SELECT bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NULL 

The above query would output a table with the id and handle for all block types that aren't used, in addition to the handle for the Matrix field they belong to, i.e.
id  | handle             | field
------------------------------------------
13  | someMatrixBlock    | someMatrixField
4   | anotherMatrixBlock | someMatrixField

To actually execute the SQL query, you can use a free tool like Sequel Pro or phpMyAdmin – or write a custom plugin that uses the QueryBuilder.
Edit
Upon request in the comments below, here's a query that will list all block types that are in use (i.e. there are blocks for each particular block type). The number of blocks for each type is listed in a count column:
SELECT COUNT(bt.id) as count, bt.id, bt.handle, f.handle as field 
FROM craft_matrixblocktypes bt 
INNER JOIN craft_fields f ON bt.fieldId = f.id 
LEFT JOIN craft_matrixblocks b ON bt.id = b.typeId 
WHERE b.id IS NOT NULL 
GROUP BY bt.id    

...and here's a query that will output a table with all blocks, sorted by block type, with the ID and URI for the entry which "owns" each particular block. The following query should work for Craft 2:
SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, i18n.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_i18n i18n on i18n.elementId = e.id
ORDER BY bt.id

For Craft 3 (and 4), you'll need to replace the craft_elements_i18n table with craft_elements_sites:
SELECT b.id as blockId, bt.handle as blockType, bt.id as blockTypeId, e.id as entryId, sites.uri as entryUri, f.handle as field
FROM craft_matrixblocks b
INNER JOIN craft_entries e on e.id = b.ownerId
INNER JOIN craft_fields f on f.id = b.fieldId
INNER JOIN craft_matrixblocktypes bt on bt.id = b.typeId
LEFT JOIN craft_elements_sites sites on sites.elementId = e.id
ORDER BY bt.id

If you want to exclude drafts, revisions and soft-deleted entries, include this in the query:
INNER JOIN craft_elements els on els.id = b.ownerId 
WHERE els.dateDeleted IS NULL AND els.draftId IS NULL AND els.revisionId IS NULL AND els.archived = 0

Edit: For Craft 4, you'll want to replace all instances of b.ownerId with b.primaryOwnerId.
If you want to limit that last query to only list the blocks for a particular block type, you can replace the ORDER BY statement with this:
WHERE bt.id = 2

Where 2 is the ID for the block type you want to list.

> **Comments:**
> **username_0**: Excellent - really useful - in a similar manner then is it possible to find the number of times a block from a particular field does appear and say what page it is on  
> **username_1**: The number of times a block type have been used (i.e. the number of blocks with that particular block type) should be easy enough, but if you want to output the ID/handle/URL for any elements with relevant blocks you're going to have to pull multiple rows for each block type (one per element), so I don't see those two operations being very useful together.  
> **username_0**: Thanks - the number of times of block type would be used would be very useful as we could see then what blocks we could do away with. If there is a few then some way to find easily what entries the block is used in - even if it needed to be a separate query as there are a large number of entries on the site so could be hard to find manually  
> **username_1**: @mmc501 Edited my answer and added a couple queries that might prove useful.  
> **username_0**: First class - easy to tidy up now - much appreciated!  
> **username_2**: @mmc501The queries you provided are SUPER helpful. Just wanted to say thank you! You helped me considerably with this answer.  
> **username_1**: @dpayne Awesome that it proved helpful :)  

---

