Sample Database queries for Movies and TV Shows

General Discussions Tips, Tricks & How-Tos
,
1

Many of us want to report on items in the Plex database and I’ve spent years hacking the database for data I want to see, so sharing here for the benefit of others.

The queries were created using “DB Browser (SQLite)” and works connected to a live or static copy of the database. Note: Connecting to a live database requires reconnecting to see updates.

Updates

  • Added Library column
  • Added queries for Video and Audio file names
  • Improved query performance
  • Fixed issues with deleted files showing in queries
  • Packaged all queries in a single Zip file

Transcoding considerations

  • Use Handbrake to create transcoded copies that are smaller with lower bitrates and supported codecs, improving playback
  • Both MPEG-2 and VC-1 can be stored much more efficiently in H.264 (AVC) or H.265 (HEVC) with H.265 being most optimal
  • VC-1 Unsupported by most streaming devices, requiring transcoding which may fail to play and cause loss of detail in very bright scenes (i.e. explosions)

Copy and paste to Excel

Select the upper left corner of the query results and then Ctrl-C to copy the contents of the grid. Next, paste into an Excel spreadsheet. Note: Column headings are not copied.

image
image1020×127 19.4 KB

Sample query results

Movie Summary

image
image1029×241 62.9 KB

Movie Details

image
image2014×246 89.1 KB

TV Show Summary

image
image757×240 41.7 KB

TV Show Details

image
image1922×243 70.8 KB

Video Files with extras

image
image2058×243 75.8 KB

Audio Files with extras

image
image2654×256 98.4 KB

Sample Plex Queries 2026-02.zip (7.4 KB)

1 Like
How can I query the Plex database to get a listing of my content?
Querying the Plex Database
How can I query the Plex database to get a listing of my content?
2

Or you could use Webtools-NG.

1 Like
3

Would you happen to know which table and field represents the “soft delete”?

4

You mean what’s in Plex‘s „Trash“?

5

Yes and no. I have a lot of “soft delete” items in my database for which the files no longer physically exist and that are no longer in trash, or at least Empty Trash does not clear them. Should this be considered a bug that I should report?

My thought is to use the “soft delete” flag to ignore and/or delete them from my queries.

6

These should be one and the same.

Plex will often not clear the trash if the database is damaged.

If the DB file is healthy, a regular reason that the trash is retained is that not all the paths listed on the “Add Folders” tab of a library are:

  • existent
  • accessible to Plex server
  • not completely empty

There is a third reason cropping up lately, but only specifically if your server is running on a Mac and uses a Synology NAS as media storage: Plex Files Unavailable but they’re not missing - #525 by scuzzbono

7

Don’t forget that if the media is in a folder/drive that is not accessible, these don’t get removed incase the location becomes available again. Recreate the folder/drive, run a scan, and done.

8

Thanks. I tried recreating the directory structures earlier today and adding it to an existing library and a new one. Unfortunately, running a scan and then emptying trash did not remove the entries. I even tried adding one of the same files and one new one. All this did was add one more “ghost” entry for the new file.

Any other ideas?

9

Run a scan of the library with the ghost entry and upload the server log.

10

Recreating the folder is not enough. It must also be not completely empty of files.

11

Testing seems to indicate you must create the same file structure and files, add them, move them out of all libraries, and then empty the trash.

It would be great to allow a delete for any unused items over some period of time (ex. 1 year, 6 months), preferably user configurable.

12

Added SQL example for displaying the path or, folder and file structure for movies and extras. Can also be used for other media types.

13

Not sure what you did, but you should only need to create the folder that your library pointed to. I.e. your movie library is to point to C:\movies, but now you changed it to C:\my movies. You only need to recreate C:\movies and place 1 file in there, any file. As long as that base folder doesn’t appear empty, Plex will remove any associated entries.