New SharePoint Views: Sites, AllLists, AllFiles

As of Version: 24.0.9190 of the CData Drivers and Connectors for SharePoint, the following new views have been added to the REST schema:

• Sites: The new Sites view retrieves all sites across the SharePoint domain.
• AllLists: Retrieves lists from all SharePoint sites and subsites within the domain.
• AllFiles: Retrieves files and folders from all libraries across all SharePoint sites and subsites within the domain.

This feature for the CData SharePoint Drivers was added in the March 2025 driver release and allows users to retrieve file information from all sites within a SharePoint domain. Previously, the CData SharePoint Drivers only accessed data from the SharePoint site that was specified in the URL connection property. Accessing data from subsites or other sites in your SharePoint domain required setting up multiple connections to each site when accessing file information from those sites. Now, with the newly added views, users can retrieve file information across all sites with a single connection configured.

In this article, we explore examples for querying these newly exposed views and review the data model going over the columns and their data types.

Example Implementation

Retrieve file information from all sites

To retrieve the file information for all files from all sites in your SharePoint domain, you can query the AllFiles view. The query below returns all file information for all files in the SharePoint domain.

SELECT * FROM AllFiles

Results:

Retrieve file information from a specific site

To retrieve all file information from a specific site, you can specify a SiteURL in a WHERE clause of your SQL statement. One way to gather the Site URLs is to query the Sites view. The following query returns the site name, site URL, and site description of all sites:

SELECT Title, SiteURL, Description FROM Sites

Results:

After retrieving the SiteURL for the Site you want to retrieve all file information from, you can include that Site URL in the WHERE clause. For example, the following query retrieves only the file information for the site 'https://cdata0.sharepoint.com/sites/product':

SELECT * FROM AllFiles WHERE SiteURL = 'https://cdata0.sharepoint.com/sites/product'

Results:

Retrieve file information from a specific library within a specific site

To retrieve all file information from a specific library within a site, you can add a LibraryId to the WHERE clause of your SQL statement. The Id for libraries can be gathered by querying the AllLists view. The AllLists view retrieves information about all lists that contain libraries. The following query returns list information, including libraries, in the site 'https://cdata0.sharepoint.com/sites/product':

SELECT Id, Title FROM AllLists WHERE SiteURL = 'https://cdata0.sharepoint.com/sites/product'

Results:

After retrieving the LibraryId for the Library you want to retrieve all file information from, you can include that Id in the WHERE clause. For example, the following query retrieves only the file information for the library with the specific LibraryId specified:

SELECT * FROM AllFiles WHERE SiteURL = 'https://cdata0.sharepoint.com/sites/product' AND LibraryId = '1342af2b-897b-4e84-91e4-717be4622dfd'

Results:

Note: Some results from the examples above have been truncated and reformatted for readability. The actual query results are larger, but only a representative subset of rows and columns is shown in the examples.

Data Models

Sites

AllLists

AllFiles