The db query would look like this:
SELECT `option_name` AS `name`, `option_value` AS `value`
FROM $wpdb->options
WHERE `option_name` LIKE '%transient_%'
ORDER BY `option_name`
To sort the results by their function (site transients, timeouts) use a function like this:
add_action( 'shutdown', function(){
global $wpdb;
$sql = "SELECT `option_name` AS `name`, `option_value` AS `value`
FROM $wpdb->options
WHERE `option_name` LIKE '%transient_%'
ORDER BY `option_name`";
$results = $wpdb->get_results( $sql );
$transients = array();
foreach ( $results as $result )
{
if ( 0 === strpos( $result->name, '_site_transient_' ) )
{
if ( 0 === strpos( $result->name, '_site_transient_timeout_') )
$transients['site_transient_timeout'][ $result->name ] = $result->value;
else
$transients['site_transient'][ $result->name ] = maybe_unserialize( $result->value );
}
else
{
if ( 0 === strpos( $result->name, '_transient_timeout_') )
$transients['transient_timeout'][ $result->name ] = $result->value;
else
$transients['transient'][ $result->name ] = maybe_unserialize( $result->value );
}
}
print '<pre>$transients = ' . esc_html( var_export( $transients, TRUE ) ) . '</pre>';
});
Now you get an array, separated by the transient functions with unserialized values.
Sample output:
$transients = array (
'site_transient' =>
array (
'_site_transient_browser_0f2bbce5647f9c092edea85f1b5d9145' =>
array (
'platform' => 'Windows',
'name' => 'Opera',
'version' => '12.02',
'update_url' => 'http://www.opera.com/',
'img_src' => 'http://s.wordpress.org/images/browsers/opera.png',
'img_src_ssl' => 'https://wordpress.org/images/browsers/opera.png',
'current_version' => '11.64',
'upgrade' => false,
'insecure' => false,
),
'_site_transient_browser_4155da8a3756e08080a06133476ef1fd' =>
array (
'platform' => 'Windows',
'name' => 'Firefox',
'version' => '19.0',
'update_url' => 'http://www.firefox.com/',
'img_src' => 'http://s.wordpress.org/images/browsers/firefox.png',
'img_src_ssl' => 'https://wordpress.org/images/browsers/firefox.png',
'current_version' => '16',
'upgrade' => false,
'insecure' => false,
),
),
'site_transient_timeout' =>
array (
'_site_transient_timeout_browser_0f2bbce5647f9c092edea85f1b5d9145' => '1352809256',
'_site_transient_timeout_browser_4155da8a3756e08080a06133476ef1fd' => '1366603648',
),
'transient' =>
array (
'_transient_feed_mod_46583134dd8a90321b20eb41cdeb134c' => '1366089834',
'_transient_feed_mod_57bc725ad6568758915363af670fd8bc' => '1352920456',
'_transient_plugins_delete_result_1' => '1',
),
'transient_timeout' =>
array (
'_transient_timeout_feed_46583134dd8a90321b20eb41cdeb134c' => '1366133033',
'_transient_timeout_feed_57bc725ad6568758915363af670fd8bc' => '1352963656',
),
)