Files
Provides detailed information about individual files stored in OneDrive drives, including name, size, and metadata.
Table Specific Information
Projection is done server-side. Also, queries that include the primary key Id and IN are executed server-side, e.g.:
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, e.g.:
SELECT * FROM Files WHERE ParentReference_DriveId = 'b!3Yqza-5FJ0usfhyH-2QafkQpf0OC0jlNh8LAETWPK6dNROPDScevTKbV-drnCFHV' And ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'
File listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).
You can specify the value -1 to get all the files in the 'Drive' no matter the depth they're located in.
While files may be listed in this view, the contents of the files may be worked with by using stored procedures such as:
- CopyResource - Copy a file/folder from one directory in your drive to another.
- DeleteResource - Delete a file/folder from your drive.
- DownloadFile - Download an existing file from your drive.
- MoveResource - Move a file/folder from one directory in your drive to another.
- UploadFile - Upload 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_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 |
Folders.Id | 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. | |
| parentReference_shareId | String | Sharing ID used to access the parent item via a shared link. | |
| parentReference_sharepointIds_listId | String | SharePoint list ID associated with the parent item. | |
| parentReference_sharepointIds_listItemId | String | SharePoint list item ID associated with the parent item. | |
| parentReference_sharepointIds_listItemUniqueId | String | Globally unique ID of the parent list item in SharePoint. | |
| parentReference_sharepointIds_siteId | String | ID of the SharePoint site containing the parent item. | |
| parentReference_sharepointIds_siteUrl | String | URL of the SharePoint site hosting the parent item. | |
| parentReference_sharepointIds_tenantId | String | ID of the Microsoft 365 tenant hosting the SharePoint site. | |
| parentReference_sharepointIds_webId | String | ID of the web that contains the parent SharePoint list. | |
| parentReference_siteId | String | ID of the SharePoint site, if the file is part of a SharePoint-hosted library. | |
| webUrl | String | Direct URL to access the file via a web browser. | |
| LinkedcreatedByUser | String | Reference to the user profile that created the file. | |
| LinkedlastModifiedByUser | String | Reference to the user profile that last modified the file. | |
| 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, 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. | |
| content | String | Binary content of the file, encoded for transfer or storage. | |
| cTag | String | Change tag used to detect updates to the file or folder. | |
| deleted_state | String | Describes whether the file is in a deleted state, such as 'softDeleted' or 'hardDeleted'. | |
| 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, such as application/pdf or image/jpeg. | |
| file_processingMetadata | Bool | Indicates whether the file is currently undergoing server-side processing. | |
| fileSystemInfo_createdDateTime | Datetime | Filesystem timestamp indicating when the file was created. | |
| fileSystemInfo_lastAccessedDateTime | Datetime | Filesystem timestamp of the last time the file was accessed. | |
| 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. | |
| folder_view_sortBy | String | Default sort field for the folder's content view, such as name or lastModifiedDateTime. | |
| folder_view_sortOrder | String | Default sort order for the folder view, either ascending or descending. | |
| folder_view_viewType | String | Type of folder view, such as thumbnails or list. | |
| 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. | |
| package_type | String | Specifies the type of package the file belongs to, such as OneNote or ZIP. | |
| pendingOperations_pendingContentUpdate_queuedDateTime | Datetime | Timestamp indicating when an update to the file was queued for processing. | |
| 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_level | String | The publication visibility level, such as draft or published. | |
| publication_versionId | String | Identifier for the published version of the file. | |
| remoteItem_createdBy_application_displayName | String | Name of the application that created the linked remote file. | |
| remoteItem_createdBy_application_id | String | ID of the application that created the remote file. | |
| remoteItem_createdDateTime | Datetime | Timestamp when the remote file was created. | |
| remoteItem_file_hashes_crc32Hash | String | CRC32 hash value for the remote file. | |
| remoteItem_file_hashes_quickXorHash | String | QuickXor hash for the remote file. | |
| remoteItem_file_hashes_sha1Hash | String | SHA-1 hash value of the remote file. | |
| remoteItem_file_hashes_sha256Hash | String | SHA-256 hash value of the remote file. | |
| remoteItem_file_mimeType | String | MIME type of the remote file, indicating the file format such as application/pdf or image/png. | |
| remoteItem_file_processingMetadata | Bool | Indicates whether the remote file is currently undergoing background processing. | |
| remoteItem_fileSystemInfo_createdDateTime | Datetime | Timestamp when the remote file was originally created in its source location. | |
| remoteItem_fileSystemInfo_lastAccessedDateTime | Datetime | Last access time for the remote file as reported by the file system. | |
| remoteItem_fileSystemInfo_lastModifiedDateTime | Datetime | Last modification timestamp of the remote file in the source system. | |
| remoteItem_folder_childCount | Int | Number of items contained within the remote folder. | |
| remoteItem_folder_view_sortBy | String | Default field used to sort items in the remote folder view, such as name or date. | |
| remoteItem_folder_view_sortOrder | String | Default sort order for the remote folder view, either ascending or descending. | |
| remoteItem_folder_view_viewType | String | Preferred display layout for the remote folder, such as list or thumbnails. | |
| remoteItem_id | String | Unique identifier of the remote item within its source drive. | |
| remoteItem_image_height | Int | Height of the remote image in pixels. | |
| remoteItem_image_width | Int | Width of the remote image in pixels. | |
| remoteItem_lastModifiedDateTime | Datetime | Last time the remote item was modified in its original location. | |
| remoteItem_name | String | Name of the remote file or folder as it appears in the source system. | |
| remoteItem_package_type | String | Package type for structured content in the remote item, such as OneNote or ZIP. | |
| remoteItem_parentReference_driveId | String | Drive ID of the remote item's parent folder. | |
| remoteItem_parentReference_driveType | String | Type of the drive containing the remote item, such as personal or documentLibrary. | |
| remoteItem_parentReference_id | String | Unique ID of the parent folder or item in the remote source. | |
| remoteItem_parentReference_name | String | Name of the parent folder or item. | |
| remoteItem_parentReference_path | String | Path to the parent folder of the remote item. | |
| remoteItem_parentReference_shareId | String | Sharing ID for accessing the remote item's parent through a shared link. | |
| remoteItem_parentReference_sharepointIds_listId | String | ID of the SharePoint list linked to the parent of the remote item. | |
| remoteItem_parentReference_sharepointIds_listItemId | String | SharePoint list item ID of the parent of the remote item. | |
| remoteItem_parentReference_sharepointIds_listItemUniqueId | String | Globally unique identifier of the SharePoint list item for the parent. | |
| remoteItem_parentReference_sharepointIds_siteId | String | ID of the SharePoint site that contains the parent item. | |
| remoteItem_parentReference_sharepointIds_siteUrl | String | Full URL of the SharePoint site containing the parent item. | |
| remoteItem_parentReference_sharepointIds_tenantId | String | Tenant ID of the Microsoft 365 environment hosting the parent item. | |
| remoteItem_parentReference_sharepointIds_webId | String | Web ID in SharePoint where the parent list or item is located. | |
| remoteItem_parentReference_siteId | String | Site ID of the SharePoint environment linked to the parent item. | |
| remoteItem_shared_scope | String | Sharing scope for the remote item, such as organization or anonymous. | |
| remoteItem_shared_sharedDateTime | Datetime | Timestamp indicating when the remote item was shared. | |
| remoteItem_size | Long | Size of the remote item in bytes. | |
| remoteItem_specialFolder_name | String | Name of the special folder associated with the remote item, such as Documents or Photos. | |
| remoteItem_video_audioBitsPerSample | Int | Number of bits used for each audio sample in the remote video file. | |
| remoteItem_video_audioChannels | Int | Number of audio channels in the remote video file, such as mono or stereo. | |
| remoteItem_video_audioFormat | String | Audio format used in the remote video file, such as AAC or MP3. | |
| remoteItem_video_audioSamplesPerSecond | Int | Audio sampling rate of the remote video file, in hertz. | |
| remoteItem_video_bitrate | Int | Bitrate of the remote video file, indicating data rate in bits per second. | |
| remoteItem_video_duration | Long | Total playback duration of the remote video in milliseconds. | |
| remoteItem_video_fourCC | String | Four-character code identifying the video codec used in the remote file. | |
| remoteItem_video_frameRate | Double | Frame rate of the remote video in frames per second. | |
| remoteItem_video_height | Int | Height of the video frame in pixels. | |
| remoteItem_video_width | Int | Width of the video frame in pixels. | |
| remoteItem_webDavUrl | String | URL used to access the remote item via WebDAV protocol. | |
| remoteItem_webUrl | String | Web browser URL to directly access the remote item. | |
| searchResult_onClickTelemetryUrl | String | URL used for telemetry tracking when a search result is clicked. | |
| shared_owner_application_displayName | String | Name of the application that owns the shared item. | |
| shared_owner_application_id | String | Unique ID of the application that owns the shared item. | |
| shared_scope | String | Scope of the sharing permissions applied to the item. | |
| shared_sharedDateTime | Datetime | Timestamp when the item was shared with others. | |
| sharepointIds_listId | String | ID of the associated SharePoint list. | |
| sharepointIds_listItemId | String | ID of the item within the SharePoint list. | |
| sharepointIds_listItemUniqueId | String | Globally unique identifier of the SharePoint list item. | |
| sharepointIds_siteId | String | ID of the SharePoint site where the item is located. | |
| sharepointIds_siteUrl | String | URL of the SharePoint site hosting the list or item. | |
| sharepointIds_tenantId | String | ID of the Microsoft 365 tenant containing the SharePoint site. | |
| sharepointIds_webId | String | ID of the SharePoint web (subsite) hosting the list or item. | |
| size | Long | Total size of the item in bytes, including metadata and content. | |
| specialFolder_name | String | Label identifying the type of special folder, such as Documents or Music. | |
| 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 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. | |
| webDavUrl | String | Direct WebDAV-compatible URL for the item, used in integrations and file transfers. | |
| Linkedanalytics | String | Link to analytics data or usage metrics associated with the item. | |
| Linkedchildren | String | Link to child items nested under the current folder or container. | |
| LinkedlistItem | String | Link to the associated SharePoint list item, if applicable. | |
| Linkedpermissions | String | Link to view or manage permissions granted on the item. | |
| Linkedsubscriptions | String | Link to any active webhook subscriptions monitoring the item. | |
| Linkedthumbnails | String | Link to available thumbnails or preview images for the item. | |
| Linkedversions | String | Link to the version history of the item, if versioning is enabled. | |
| Linkedworkbook | String | Link to the Excel workbook view or API for the item, if applicable. |