Files
Provides detailed information about individual files stored in OneDrive drives, including name, size, and metadata.
View-Specific Information
Select
SQL projection is performed server-side. In addition, queries that use the primary key Id with an IN filter are also executed server-side. For example:
SELECT Id, Name, audio_album, audio_title FROM Files WHERE Id IN ('B1E6BJ084A9133KB1!101', 'APF9D2K6WFA524Y')
Include ParentReference_DriveId and ParentReference_Id in the WHERE clause to get the result for a particular folder. For example:
SELECT * FROM Files WHERE ParentReference_DriveId = 'b!3Yqza-5FJ0usfhyH-2QafkQpf0OC0jlNh8LAETWPK6dNROPDScevTKbV-drnCFHV' And ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'
Server-side filtering using ParentReference_Id is supported on the root drive when the AuthScheme property is set to AzureAD. However, it is not available for client-credential authentication. For example:
SELECT * FROM Files WHERE ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'
For server-side filtering using ParentReference_Id with client-credential authentication, such as AzureServicePrincipal or AzureServicePrincipalCert, the createdBy_user_id column is required. For example:
SELECT * FROM Files WHERE ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH' AND createdBy_user_id = '1c67f23f-8681-4c67-9c19-253e628442d5'
UserId and GroupId connection properties can also be used to filter records. If both are included in the connection string, UserId takes precedence over GroupId.
To filter files for a specific user:
SELECT * FROM Files WHERE UserId = 'a9920804-3212-4f9d-aac7-f55c697fa2bc'
To filter files for a specific group:
SELECT * FROM Files WHERE GroupId = '0a98c469-e041-4d3b-91b5-0966e5200081'
File Listing
File listing is performed recursively based on the DirectoryRetrievalDepth property. By default, this property is set to 5, which means the add-in retrieves data only for the first six levels of the selected drive. This is because level 1 corresponds to a DirectoryRetrievalDepth of 0. You can set the value to -1 to retrieve all the files in the drive, regardless of their depth.
Files can be listed in this view, and their contents can be accessed using stored procedures such as:
- CopyResource: Copies a file/folder from one directory in your drive to another.
- DeleteResource: Deletes a file/folder from your drive.
- DownloadFile: Downloads an existing file from your drive.
- MoveResource: Moves a file/folder from one directory in your drive to another.
- UploadFile: Uploads a new file or update content to an existing file.
Columns
| Name | Type | References | Description |
| id [KEY] | String | Unique identifier of the file item within the OneDrive drive. | |
| Etag | String | Entity tag for tracking changes to the file; used for concurrency control. | |
| createdBy_user_id | String | Identifier of the user who originally created the file. | |
| createdBy_user_displayName | String | Display name of the user who created the file. | |
| lastModifiedBy_user_id | String | Identifier of the user who last modified the file. | |
| lastModifiedBy_user_displayName | String | Display name of the user who last modified the file. | |
| ResourceType | String | Specifies the type of resource represented, such as file or folder. | |
| createdBy_user_email | String | Email of the user who created the file. | |
| lastModifiedBy_user_email | String | Email of the user who last modified the file. | |
| createdBy_application_displayName | String | Name of the application that created the file. | |
| createdBy_application_id | String | Unique ID of the application that created the file. | |
| createdDateTime | Datetime | Timestamp when the file was initially created. | |
| description | String | User-defined or system-generated description of the file's purpose or contents. | |
| lastModifiedBy_application_displayName | String | Name of the application that last modified the file. | |
| lastModifiedBy_application_id | String | Unique ID of the application that last modified the file. | |
| lastModifiedDateTime | Datetime | Timestamp of the most recent modification to the file. | |
| name | String | The file's name as it appears in OneDrive. | |
| parentReference_driveId | String | ID of the parent drive containing the file. | |
| parentReference_driveType | String | Type of the parent drive, such as personal or documentLibrary. | |
| parentReference_id | String | Unique ID of the parent folder or item. | |
| parentReference_name | String | Name of the parent folder or item. | |
| parentReference_path | String | File system path of the parent item within OneDrive. | |
| webUrl | String | Direct URL to access the file via a web browser. | |
| audio_album | String | Name of the album the audio file belongs to. | |
| audio_albumArtist | String | Name of the album artist associated with the audio file. | |
| audio_artist | String | Primary artist of the audio file. | |
| audio_bitrate | Long | Bitrate of the audio file in bits per second. | |
| audio_composers | String | Comma-separated list of composers (for example, John Williams, Hans Zimmer, or Ennio Morricone). | |
| audio_copyright | String | Copyright information associated with the audio file. | |
| audio_disc | Int | Disc number the track is from, if part of a multi-disc album. | |
| audio_discCount | Int | Total number of discs in the audio collection. | |
| audio_duration | Long | Duration of the audio file in milliseconds. | |
| audio_genre | String | Genre of the audio file content, such as Rock, Jazz, or Classical. | |
| audio_hasDrm | Bool | Indicates whether the audio file is protected by digital rights management. | |
| audio_isVariableBitrate | Bool | Specifies whether the audio file uses variable bitrate encoding. | |
| audio_title | String | Title of the audio track. | |
| audio_track | Int | Track number of the audio file on the disc. | |
| audio_trackCount | Int | Total number of tracks in the audio collection or album. | |
| audio_year | Int | Year the audio file was published or recorded. | |
| cTag | String | Change tag used to detect updates to the file or folder. | |
| file_hashes_crc32Hash | String | CRC32 checksum used for data validation of the file. | |
| file_hashes_quickXorHash | String | QuickXor hash for efficient change detection in Office files. | |
| file_hashes_sha1Hash | String | SHA-1 cryptographic hash of the file. | |
| file_hashes_sha256Hash | String | SHA-256 cryptographic hash of the file. | |
| file_mimeType | String | MIME type of the file. | |
| fileSystemInfo_createdDateTime | Datetime | Filesystem timestamp indicating when the file was created. | |
| fileSystemInfo_lastModifiedDateTime | Datetime | Filesystem timestamp of the last time the file was modified. | |
| folder_childCount | Int | Number of items directly within the folder, if the item is a folder. | |
| image_height | Int | Height of the image in pixels. | |
| image_width | Int | Width of the image in pixels. | |
| location_altitude | Double | Altitude at which the photo or file was captured, in meters. | |
| location_latitude | Double | Latitude coordinate of where the photo or file was captured. | |
| location_longitude | Double | Longitude coordinate of where the photo or file was captured. | |
| photo_cameraMake | String | Manufacturer of the camera used to take the photo. | |
| photo_cameraModel | String | Model of the camera used to capture the photo. | |
| photo_exposureDenominator | Double | Denominator of the exposure time ratio used to capture the photo. | |
| photo_exposureNumerator | Double | Numerator of the exposure time ratio used to capture the photo. | |
| photo_fNumber | Double | Aperture setting (f-number) used when the photo was taken. | |
| photo_focalLength | Double | Focal length of the lens in millimeters. | |
| photo_iso | Int | ISO sensitivity setting used when the photo was taken. | |
| photo_orientation | Int | Numeric value representing the photo's orientation (for example, 1 for normal, 3 for rotated 180). | |
| photo_takenDateTime | Datetime | Date and time when the photo was captured. | |
| publication_checkedOutBy_application_displayName | String | Display name of the user who checked out the file. | |
| publication_checkedOutBy_application_id | String | Identifier of the user who checked out the file. | |
| publication_level | String | The publication visibility level, such as checkout or published. | |
| publication_versionId | String | Identifier for the published version of the file. | |
| size | Long | Total size of the item in bytes, including metadata and content. | |
| video_audioBitsPerSample | Int | Bits per audio sample in the video file. | |
| video_audioChannels | Int | Number of audio channels in the video, typically 1 for mono or 2 for stereo. | |
| video_audioFormat | String | Encoding format used for the audio stream in the video file. | |
| video_audioSamplesPerSecond | Int | Sample rate for the video's audio stream, in hertz. | |
| video_bitrate | Int | Total data rate of the video stream, measured in bits per second. | |
| video_duration | Long | Playback duration of the video in milliseconds. | |
| video_fourCC | String | Four-character code (FourCC) identifying the video codec (such as H264 or XVID). | |
| video_frameRate | Double | Number of frames displayed per second in the video. | |
| video_height | Int | Height of the video in pixels. | |
| video_width | Int | Width of the video in pixels. |