sp_list_directory system procedure

sp_list_directory(
root_path
[, max_depth ]
)

root_path Use this LONG NVARCHAR parameter to specify the absolute or relative directory path. The relative path is relative to the working directory of the server. When the sandbox feature is enabled, the absolute and relative paths refer to the directory where the main database file is located.
max_depth Use this optional INTEGER parameter to specify the maximum number of directories to traverse. A max_depth of NULL, 0, or a negative value results in all subdirectories of root_path being traversed. The default is NULL.

This procedure returns information about the files and subdirectories in a specified directory. This procedure returns a table with the following columns:

Column name	Column description
file_path	LONG NVARCHAR - The path to a file or subdirectory within the specified directory. If `directory_path` is specified as a relative path, then the returned file_path value is relative. Otherwise, the file_path value is absolute. When the sandbox feature is enabled, the absolute and relative paths refer to the directory where the main database file is located.
file_type	NVARCHAR(1) - F if the file_path value is a file or D when the file_path value is a directory.
`file_size`	UNSIGNED BIGINT - Specifies the size in bytes of the file or NULL when file_path value is a directory
owner	NVARCHAR(128) - The owner of the file or directory.
create_date_time	TIMESTAMP WITH TIME ZONE - The date and time the file or directory was created, returned in the database server's timezone.
modified_date_time	TIMESTAMP WITH TIME ZONE - The date and time the file or directory was last modified, returned in the database server's timezone.
access_date_time	TIMESTAMP WITH TIME ZONE - The date and time the file or directory was last accessed, returned in the database server's timezone.
permissions	VARCHAR(10) - The set of access permissions for the file or directory.

The max_depth parameter specifies the maximum depth that the directory will be traversed. A max_depth of NULL, 0, or a negative value implies that there is no restriction.

You must have the READ FILE system privilege.

SQL/2008 Vendor extension.

The following statement lists the files and subdirectories of the c:\sqlany\samples\SQLAnywhere directory.

CALL sp_list_directory('c:\\sqlany\\samples\\SQLAnywhere');

The following statement returns the total number of files and folders in the c:\sqlany\samples\SQLAnywhere and c:\sqlany\samples\UltraLite directories.

SELECT COUNT(*) FROM sp_list_directory( 'c:\\sqlany\\samples\\SQLAnywhere' )
UNION ALL
SELECT COUNT(*) FROM sp_list_directory( 'c:\\sqlany\\samples\\UltraLite' );

If both the c:\sqlany\samples\SQLAnywhere and c:\sqlany\samples\UltraLite directories contain the same file names, file types, and file sizes, then the following statement combines the results from two directories and gives you the total number of unique results.

SELECT COUNT(*) FROM 
(
    SELECT REPLACE( file_path, 'c:\\sqlany\\samples\\SQLAnywhere', '' ) AS file_path, file_type, file_size 
    FROM sp_list_directory( 'c:\\sqlany\\samples\\SQLAnywhere' )
    UNION
    SELECT REPLACE( file_path, 'c:\\sqlany\\samples\\UltraLite', '' ) AS file_path, file_type, file_size 
    FROM sp_list_directory( 'c:\\sqlany\\samples\\UltraLite' )
) d;

sp_list_directory system procedure

Syntax

Parameters

Result set

Remarks

Privileges

Standards and compatibility

See also

Examples