A question recently came up over in the Flickr Lightroom group about generating a list of all filenames or selected filenames in a catalog. There’s a pretty nice straightforward bit of SQL that will produce just such a list.
My new favorite tool for poking around in the Lightroom database is the SQLite Manager extension for Firefox. It plugs seamlessly into Firefox and is a very good tool—faster than the old sqlite3 browser, and with the added bonus of not requiring Rosetta to be installed under Snow Leopard. Plug the following code into the “Execute SQL” box (or kick it old school via the command line sqlite3 tool):
You can do even better by using the sqlite3 concatenate operate in the select statement:
That will produce a nicely-formatted list of files:
2007-02-19/IMGP1609.PEF 2007-02-19/IMGP1610.PEF 2007-02-19/IMGP1611.PEF 2007-02-19/IMGP1612.PEF 2007-02-19/IMGP1613.PEF 2007-02-19/IMGP1614.PEF
Still building, we can add in the absolutePath field to get a complete path — very useful for when you have files stored in multiple locations, such as offline storage or backup locations.
And, finally, because how often do you really need to see the paths for all of the files in your library, really?, we can limit the query to just files in a quick collection. This takes a couple of extra steps, because we can’t directly get to the keyword data from the path data:
And there you have it. One more fun trick you can pull thanks to the sqlite3 database that underlies your Lightroom 2 catalog.
Check out the rest of my Lightroom posts for much, much more, including more database tinkering, keywording, and workflow.