LIST OBJECTS ​

Return a complete or partial list of the objects that are stored in a specific external location.

The LIST OBJECTS command returns the name of each object, its size (in bytes), when it was created, and when it was last modified. The size and creation time may not be available for all objects.

LIST OBJECTS
EXTERNAL LOCATION name
[ PREFIX (prefix) ]
[ SUFFIX (suffix) ]
[ LIMIT limit ]
[ START AFTER 'start_key' ]

EXTERNAL LOCATION

Name of an external location object. See CREATE EXTERNAL LOCATION.

PREFIX

Optional prefix string that filters the list of objects. You can specify a folder name or a common prefix that is used for a set of files. For example:

PREFIX ('healthcare')
PREFIX ('healthcare/allergies')
PREFIX ('healthcare/allergies_10')

SUFFIX

Optional suffix string that filters the list of objects. For example, you can specify a file extension or a character such as \ (the default suffix in the Load Data Assistant):

SUFFIX('.csv')
SUFFIX('.gz')
SUFFIX('\')

LIMIT

An integer up to 1000 that limits the number of objects returned, given the filters applied.

START AFTER

A string that refers to an object name (or key name), which is used as a starting point for the list. For example:

START AFTER 'premdb/newteam.csv'

You can use this option to request the next set of objects when you reach the limit of 1000.

Note that the string specified here is not interpreted relative to the PREFIX string.

Examples ​

For example, list objects that have the premdb prefix within the premdbs3data location. The list consists of six objects, including the empty premdb folder:

The following examples show how the PREFIX, SUFFIX, LIMIT, and START AFTER options work; they can be used in various combinations. The output for these examples is copied from the Clipboard in CSV format.

Return objects that begin with premdb and end with csv:

list objects 
external location premdbs3data
prefix('premdb')
suffix('csv');

"object_name","size_bytes","creation_time","modify_time"
"premdb/match.csv",313142,,"2021-04-27T19:17:46-07:00"
"premdb/newmatchstats25mil.csv",629210880,,"2021-05-04T16:36:27-07:00"
"premdb/newteam.csv",3026,,"2021-04-27T19:17:43-07:00"
"premdb/player.csv",1286,,"2021-04-27T19:17:42-07:00"
"premdb/season.csv",746,,"2021-04-27T19:17:43-07:00"

Return the first 3 objects that start with premdb and end with csv:

list objects 
external location premdbs3data
prefix('premdb')
suffix('csv')
limit 3;

"object_name","size_bytes","creation_time","modify_time"
"premdb/match.csv",313142,,"2021-04-27T19:17:46-07:00"
"premdb/newmatchstats25mil.csv",629210880,,"2021-05-04T16:36:27-07:00"
"premdb/newteam.csv",3026,,"2021-04-27T19:17:43-07:00"

Starting after premdb/newteam.csv, return objects that begin with premdb and end with csv:

list objects external location premdbs3data
prefix('premdb/')
suffix('csv')
start after 'premdb/newteam.csv';

"object_name","size_bytes","creation_time","modify_time"
"premdb/player.csv",1286,,"2021-04-27T19:17:42-07:00"
"premdb/season.csv",746,,"2021-04-27T19:17:43-07:00"

See also Loading a Table via the Load Assistant.

Parent topic:SQL Commands