AllFiles

Retrieves all files and folders across all document libraries on a SharePoint site, including metadata details. Useful for auditing, bulk processing, and data migration.

Table Specific Information

Select

Retrieve all items from sites listed in the [Sites] table.

SELECT [*] 
FROM [AllFiles];

Retrieve all items from the specified site URL.

SELECT [*] 
FROM [AllFiles] 
WHERE [SiteURL] = 'https://yourdomain.sharepoint.com/';

Retrieve all items from a specific library within a given site.

SELECT [*] 
FROM [AllFiles] 
WHERE [SiteURL] = 'https://yourdomain.sharepoint.com/sites/YourSite' 
  AND [LibraryId] = 'YourLibraryId';

Retrieve specific columns for a file within a specific library and site.

SELECT [SiteURL], [LibraryId], [FileId], [Name] 
FROM [AllFiles] 
WHERE [SiteURL] = 'https://yourdomain.sharepoint.com/sites/YourSite' 
  AND [Name] = 'YourFileName.txt';

Retrieve all items from specific libraries in a given site.

SELECT [*] 
FROM [AllFiles] 
WHERE [SiteURL] = 'https://yourdomain.sharepoint.com/sites/YourSite' 
  AND [LibraryId] IN ('LibraryId1', 'LibraryId2', 'LibraryId3');

Columns


Name	Type	References	Description
SiteURL [KEY]	String	Sites.SiteURL	The full URL of the SharePoint site where the file is located. Useful for identifying the site context of the file.
LibraryId [KEY]	String		The unique identifier of the document library containing the file. Helps in filtering files based on specific libraries.
FileId [KEY]	Int		A unique numeric identifier assigned to the file within SharePoint. Useful for referencing files programmatically.
Name	String		The name of the file, including its extension. Important for identifying and organizing files.
Title	String		The title metadata of the file, which may be different from the file name. Often used for user-friendly file descriptions.
FileSize	String		The size of the file in bytes. Useful for tracking storage usage and managing large files.
FileType	String		The file extension or type, such as .docx, .pdf, or .xlsx. Helps in categorizing and filtering files by format.
ItemType	Int		Indicates the type of SharePoint item. Possible values: Invalid (-1), File (0), Folder (1), Web (2). Helps differentiate between files and folders.
Description	String		A more detailed description of the file, if provided. Useful for adding context to files beyond their name and title.
Path	String		The file path within the SharePoint library. Helps in locating files in a structured hierarchy.
ServerRelativeURL	String		The URL of the file relative to the SharePoint site root. Useful for internal linking within SharePoint.
EncodedAbsoluteURL	String		The full absolute URL of the file, encoded for use in web applications. Essential for accessing files externally.
CheckedOutTo	Int		The user ID of the person who has checked out the file. Helps track document ownership and editing control.
CheckInComment	String		A comment provided when the file was last checked in. Useful for tracking version history and changes.
Version	String		The version number of the file. Helps in managing document revisions and retrieving previous versions.
ContentTypeID	String		The unique identifier of the content type associated with the file. Important for metadata management and workflow automation.
UniqueId	String		A SharePoint-generated unique identifier for the file. Useful for referencing files in workflows and API requests.
GUID	String		The global unique identifier (GUID) assigned to the file. Ensures distinct identification across SharePoint environments.
Created	Datetime		The date and time when the file was initially created. Useful for tracking document lifecycle and auditing purposes.
CreatedBy	String		The username of the person who created the file. Important for monitoring authorship and accountability.
Modified	Datetime		The date and time when the file was last modified. Helps track recent changes and updates.
ModifiedBy	String		The username of the person who last modified the file. Useful for tracking recent contributions.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ItemCount	Int		Serves as a filter that will be propagated to the AllLists view.

ODBC Driver for Microsoft SharePoint

AllFiles

Table Specific Information

Select

Columns

Pseudo-Columns