File listing

File listing#

The built-in builtin.AllFiles table lists every file Querona can read in the connection’s folders, one row per file. Use it to inventory files or to find them by name, extension or size.

The AllFiles table#

Column name

Column type

Description

Name

NVarChar(255) not null

File name with extension

Type

NVarChar(30) not null

File extension with leading dot (.ext)

SizeBytes

BigInt not null

File size in bytes

Path

NVarChar(4000) not null

OS file path

Folders are not listed — every row is a file.

Which files are listed#

AllFiles covers exactly the files the connection can read, which is the union of two sets:

  • file types read without any configuration — PDF (.pdf) and e-mail (.msg, .eml). These have no extension setting, so they are always listed.

  • the extensions configured on the connection — the delimited-file, text-file and XML-file extension lists (.csv, .tsv, .txt and .xml out of the box). Add an extension to the matching list and its files appear here too; this is also how QVD (.qvd) and Parquet (.parquet) files are listed, since Querona reads them only when their extension is configured.

Leaving an extension list empty is not a restriction but the opposite: an empty list matches every file, so the connection reads — and AllFiles lists — everything in its folders. Fill all three lists in to restrict both.

AllFiles is always a superset of everything else the connection exposes: every file returned by builtin.TextFiles, builtin.XmlFiles, builtin.PdfDocuments or builtin.EmailMessages, and every file that maps to its own virtual table, also has a row here. Comparing the two is a quick way to see what a type-specific table skipped:

-- text files present in the folders but not picked up as text
SELECT a.Path FROM builtin.AllFiles AS a
 WHERE a.Type = '.txt'
   AND NOT EXISTS (SELECT 1 FROM builtin.TextFiles AS t WHERE t.Path = a.Path);

Reading the file list#

-- every readable file in the connection
SELECT Name, Type, SizeBytes, Path FROM builtin.AllFiles;

-- only PDFs, largest first
SELECT Name, SizeBytes FROM builtin.AllFiles WHERE Type = '.pdf' ORDER BY SizeBytes DESC;

AllFiles carries no file content — for that, query the type-specific table (builtin.PdfDocuments, builtin.TextFiles, …) or read the file with OPENROWSET (BULK ...).

See also#