The CData ODBC Driver for Microsoft SharePoint models Microsoft SharePoint data as an easy-to-use SQL database with tables, views, and stored procedures. Live connectivity to these objects means that any changes to your Microsoft SharePoint account are immediately reflected in the driver.

Tables

The driver can expose custom lists from Microsoft SharePoint that are not mentioned in the Tables. The data model illustrates a sample of what your SharePoint site might look like. The actual data model will be obtained dynamically based on your user credentials and SharePoint site.

Common tables include:

Table	Description
Attachments	Manages attachments for SharePoint list items, allowing retrieval and deletion. Essential for users who frequently handle file attachments within SharePoint.
Groups	Allows the creation, modification, deletion, and retrieval of SharePoint security groups. Essential for managing user permissions and access control.
Roles	Allows the creation, modification, deletion, and retrieval of SharePoint roles and permission assignments. Useful for customizing access control.
Users	Manages SharePoint users, allowing updates, deletions, and retrieval of user details. Important for keeping SharePoint user management up-to-date.
Views	Lists all subsites within a SharePoint site, including hierarchy details. Helps with site navigation and organization.

Views

Typically, entities that cannot be modified are represented as Views, or read-only tables.

Common views include:

Table	Description
FileVersions	Lists all available versions of a document stored in SharePoint, including version history details. Useful for tracking changes and restoring previous document versions.
GetValidTerms	Retrieves a list of valid managed metadata terms associated with a specific column in a SharePoint list. Helps enforce consistent categorization and tagging of SharePoint content.
Lists	Retrieves metadata about all SharePoint lists available on the site, including properties and settings. Useful for understanding the structure and usage of SharePoint lists.
Permissions	The permissions for a site or list. Note: If ItemId is empty, set the ObjectType to List or Web (an ObjectName must be specified when the ObjectType is List). If not, you must specify the ObjectName along with the ItemID.
Subsites	Allows the creation, modification, deletion, and retrieval of SharePoint roles and permission assignments. Useful for customizing access control.

You can also access custom views of a list as relational views. To get data from a custom view of a list, you can set the ViewID pseudo column in the WHERE clause.

SELECT * FROM ListName WHERE ViewID='ID of the view'

You can get the ID of the view from the Views list. You must specify the List pseudo column to get a list of views for that list. For instance:

SELECT * FROM Views WHERE List ='ListName'

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including managing users, documents, and attachments.