This page provides a guide to Establishing a Connection to Box in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Box

Establishing a Connection shows how to authenticate to Box and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Box through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Box by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Box

The Cloud enables access to metadata for Box tables and folders. Note that the Cloud cannot update the contents of files stored on Box or model file content as tables and columns.

Authenticating to Box

The Cloud uses the OAuth authentication standard to connect to Box from either a User account or a Service account.

Box provides embedded OAuth credentials that simplify connection from a Desktop application . To connect from a Web application, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

The following subsections describe how to authenticate to Box from the available OAuth flows. For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that already have embedded OAuth credentials, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in Box, see Connection.

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Service Account (OAuthJWT)

Service accounts have silent authentication, which does not require user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes to the Cloud.

To use a service account, you must create and authorize a custom OAuth application, as described in Creating a Custom OAuth Application. You can then connect to whatever Box data the service account is permitted to access.

After setting the following connection properties, you are ready to connect:

• AuthScheme: OAuthJWT.
• OAuthClientId: The client Id noted in your custom OAuth application settings.
• OAuthClientSecret: The client secret noted in your custom OAuth application settings.
• OAuthJWTCertType: PEMKEY_FILE.
• OAuthJWTCert: The path to the .pem file you generated.
• OAuthJWTCertPassword: The password of the .pem file.
• OAuthJWTCertSubject: The subject of the certificate. If you want to select the first certificate in the Certificate Store, set an * (asterisk).
• OAuthJWTSubjectType: The same as what you specified in your Application Access Values; either "enterprise" or "user". The default value of this connection property is "enterprise".
• OAuthJWTSubject: If your subject type is set to enterprise, this should be your enterprise Id. If your subject type is set to user, this should be your application user Id.
• OAuthJWTPublicKeyId: The Id of your public key in your application settings.

When you connect the Cloud completes the OAuth flow for a service account.

BOXJSON OAuthJWTCertType

To authenticate using a service account and the BOXJSON OauthJWTCertType, set these properties:

• AuthScheme: OAuthJWT.
• OAuthJWTCertType BOXJSON. OAuthJWTCert: The path to the Box-provided JSON file.

In cases where it may not be feasible to use a file in your system. you can copy the contents of the JSON file directly into the connection string. Set these properties:

• AuthScheme: OAuthJWT.
• OAuthJWTCertType: BOXJSONBLOB.
• OAuthJWTCert: The contents of the Box-provided JSON file.

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting via a .

You may choose to use your own OAuth Application Credentials when you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Procedure

This procedure creates a custom OAuth application, registers that application, and generates values that are used to configure the OAuthClientId and OAuthClientSecret.

At the Box Enterprise Developer Console:

1. Log in to your Box developers dashboard.
2. Click Create New App.
3. Specify basic application information, as appropriate.
4. Specify your application type (e.g., Custom App).
5. Select the User Authentication (OAuth 2.0) authentication method.
6. Set the Redirect URI:
   • If this is a , set the Redirect URI to http://localhost:33333 or a different port number.
7. Click Create App.
8. The next task is to create a public and private key pair.
   • To create a keypair from the Developer Console:
     1. Navigate to the Developer Console Configuration tab.
     2. Scroll down to Add and Manage Public Keys.
     3. Click Generate a Public/Private Keypair. Box creates a keypair in a JSON file, and downloads that file to your desktop. You can then move that file to your application code.

        Note: Box does not back up private keys for security reasons. Be careful to back up the Public/Private JSON file. If you lose your private key, you must reset the entire keypair.

   • To add a keypair manually:
     1. Open a terminal window and run the following OpenSSL commands:
        

        openssl genrsa -des3 -out private.pem 2048
        openssl rsa -in private.pem -outform PEM -pubout -out public.pem

        Note: To run OpenSSL in a Windows environment, install the Cygwin package.

     2. At the Developer Console, navigate to the configuration tab for the Custom OAuth application you just created.
     3. Scroll down to Add and Manage Public Keys.
     4. Click Add a Public Key.
     5. Click Verify and Save.
9. Before the custom application can be used, a Box Admin must authorize it within the Box Admin Console.
   1. Navigate to your application within the Developer Console.
   2. Click the Authorization tab.
   3. At the prompt to Submit app for authorization for access to the Enterprise, click Review and Submit.
      Your Box Enterprise Admin approves the application.
10. Finally, select the scope of user permissions your custom OAuth application must request.

After your application is created and registered, click Configuration from the main menu to access your settings. Note the displayed Redirect URI, Client ID, and Client Secret. You will need these values later.

When JWT Access Scopes Change

If you change the JWT access scopes, you must reauthorize the application in the enterprise admin console:

1. Click Apps in the main manu.
2. Select the ellipsis button next to your JWT application name.
3. Select Reauthorize App in the menu.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models Box data as an easy-to-use SQL database. There are three parts to the data model: tables, views, and stored procedures.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples of what you might have access to in your Box account.

Common tables include:

Table	Description
Bookmarks	Stores and manages bookmarks that reference web links saved within Box, allowing users to create, update, delete, and retrieve them.
Collaborations	Tracks collaboration settings on Box items such as files and folders, including user roles and permissions for shared access.
Collections	Displays collections of items that users have grouped together in Box for easier navigation and organization.
Comments	Contains user comments made on Box files and folders, supporting creation, retrieval, updates, and deletion.
FileMetadataInstances	Retrieves metadata applied to specific files, showing key-value fields associated with metadata templates.
FileVersions	Lists all available versions of a file stored in Box, enabling tracking of file history and access to prior revisions.
Folders	Represents folders in Box, including their structure, contents, and metadata, with full create, update, delete, and query capabilities.
Groups	Manages groups of users in Box for easier collaboration and permission assignment across files and folders.
LegalPolicies	Stores legal hold policies created by an enterprise to preserve content for compliance or litigation purposes.
Memberships	Defines user memberships in groups, linking users to groups and managing their access within Box.
MetadataTemplates	Contains reusable metadata templates that define structured fields which can be applied to files and folders.
RecentItems	Shows a list of items recently accessed by the user, such as files and folders opened in Box.
SharedItems	Retrieves items that have been shared, including their shared links and associated access permissions.
SignRequests	Lists electronic signature requests sent or received through Box Sign, including request status.
TaskAssignments	Represents assignments of tasks within Box, linking a task to the users responsible for completing it.
Tasks	Stores tasks created in Box for files and folders, supporting assignment, due dates, and completion tracking.
TrashedItems	Contains items moved to the trash, allowing users to view, query, or permanently delete trashed files and folders.
UserEvents	Provides a record of user activity events in Box, such as logins, file uploads, shares, and collaborations.
Users	Stores user account details in Box, supporting creation, management, and retrieval of users across the enterprise.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard create, read, update, delete (CRUD) operations, including downloading, uploading, and copying files.

When they are specified in the SQL statement, the Cloud tries to push LIMIT and OFFSET clauses to the server where possible. However, the maximum supported value for LIMIT is 1000.

Some tables do not offer support for LIMIT and OFFSET clauses. For these tables and when the global search endpoint is hit, these clauses are evaluated client-side.

You can use ExposeCollectionViews to expose your Box collections as separate views and query the metadata information of their files and folders. The generated view names are prefixed with Collection_.

You can also use AsUserId to expose the collections of a specific user and IncludeCustomFields to expose custom metadata fields as columns on the generated views.

The Cloud models the data in Box as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Box Tables

Name	Description
Bookmarks	Stores and manages bookmarks that reference web links saved within Box, allowing users to create, update, delete, and retrieve them.
Collaborations	Tracks collaboration settings on Box items such as files and folders, including user roles and permissions for shared access.
Comments	Contains user comments made on Box files and folders, supporting creation, retrieval, updates, and deletion.
Files	Represents files stored in Box, including their content, metadata, ownership, and lifecycle information.
Folders	Represents folders in Box, including their structure, contents, and metadata, with full create, update, delete, and query capabilities.
Groups	Manages groups of users in Box for easier collaboration and permission assignment across files and folders.
LegalPolicies	Stores legal hold policies created by an enterprise to preserve content for compliance or litigation purposes.
Memberships	Defines user memberships in groups, linking users to groups and managing their access within Box.
TaskAssignments	Represents assignments of tasks within Box, linking a task to the users responsible for completing it.
Tasks	Stores tasks created in Box for files and folders, supporting assignment, due dates, and completion tracking.
TrashedItems	Contains items moved to the trash, allowing users to view, query, or permanently delete trashed files and folders.
Users	Stores user account details in Box, supporting creation, management, and retrieval of users across the enterprise.

Stores and manages bookmarks that reference web links saved within Box, allowing users to create, update, delete, and retrieve them.

Table Specific Information

Select

If you query Bookmarks without specifying any condition in the WHERE Clause, only bookmarks up to 5 levels deep from the root folder are returned by default. You can change the default depth value in the connection string (for example, DirectoryRetrievalDepth=10).

SELECT * FROM Bookmarks;

By default, the search starts from the root directory, identified as directory '0'. To customize the starting directory for your search, specify its Id using the SearchRootId column.

SELECT * FROM Bookmarks WHERE SearchRootId = '293533136411';

Retrieve a bookmark by specifying the Id:

SELECT * FROM Bookmarks WHERE Id = '8397822180';

To search all the bookmarks in your enterprise, query the Bookmarks table with the Search Terms column.

SELECT * FROM Bookmarks WHERE SearchTerms = 'CData' AND OwnerUserIds = '311997108,1';

To search all the bookmarks within a specific folder, query the Bookmarks table with the ParentId column.

SELECT * FROM Bookmarks WHERE ParentId = 62297447480;

Note: When using a ParentId filter, avoid specifying a SearchRootId simultaneously. If both are used, the search will recursively start from the specified SearchRootId instead of the ParentId, which may result in slower performance.

If you're authenticated as an administrator with user impersonation permissions, you can query bookmarks from multiple user accounts:

SELECT * FROM Bookmarks WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query bookmarks associated with their account.
• Bookmarks shared with multiple users are only returned for one of the users.

Insert

To insert a bookmark you must specify at least the URL and ParentId column.

INSERT INTO Bookmarks (Url, Name, Description, ParentId) VALUES ('https://www.cdata.com/', 'CData', 'Visit CData!', 0);

Update

Any column where ReadOnly=False can be updated.

UPDATE Bookmarks SET Name = 'CData Updated Bookmark', ParentId = 62297447480  WHERE Id = 8397822180;

Delete

Delete a bookmark by specifying its Id. This bookmark is then moved to TrashedItems.

DELETE FROM Bookmarks WHERE Id = 8397822180;

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the bookmark within Box.
Url	String	False		The web address the bookmark points to, used to link to external resources.
Name	String	False		The display name given to the bookmark.
Description	String	False		A user-provided description of the bookmark's purpose or content.
ParentId	String	False	Folders.Id	Identifier of the folder that contains the bookmark, defining its location in Box.
CreatedAt	Datetime	True		The date and time when the bookmark was originally created in Box.
CreatedById	String	True	Users.Id	Identifier of the user who created the bookmark.
CreatedByName	String	True		The full name of the user who created the bookmark.
CreatedByLogin	String	True		The login email of the user who created the bookmark.
ModifiedAt	Datetime	True		The date and time when the bookmark was last updated.
ModifiedById	String	True	Users.Id	Identifier of the user who last modified the bookmark.
ModifiedByName	String	True		The full name of the user who last modified the bookmark.
ModifiedByLogin	String	True		The login email of the user who last modified the bookmark.
OwnedById	String	True	Users.Id	Identifier of the user who owns the bookmark.
OwnedByName	String	True		The full name of the user who owns the bookmark.
OwnedByLogin	String	True		The login email of the user who owns the bookmark.
Etag	String	True		Version identifier for the bookmark used to detect changes and manage concurrency.
SequenceId	String	True		An incremental identifier representing the version sequence of the bookmark.
SharedLink	String	True		A shareable URL generated to provide access to the bookmark.
ItemStatus	String	True		The current state of the bookmark, such as active or trashed.
Path	String	True		The full folder path leading to the bookmark within the Box hierarchy.
AsUserId	String	False		Identifier of the user to impersonate when making API calls, available only for Admin, Co-Admin, and Service Accounts.
SearchRootId	String	True		The folder identifier that defines the starting point for recursive searches, with '0' indicating the root folder.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
SearchTerms	String	Keywords used to search bookmarks stored in Box.
Scope	String	The scope of the search, such as limiting results to a user or enterprise. The allowed values are user_content, enterprise_content.
OwnerUserIDs	String	A list of user identifiers separated by a comma, used to restrict searches to bookmarks owned by specific users.
AncestorFolderIDs	String	A list of folder identifiers separated by a comma, used to restrict searches to bookmarks contained within specific folders.

Tracks collaboration settings on Box items such as files and folders, including user roles and permissions for shared access.

Table Specific Information

Collaborations are connections between Box items (files and folders) and Groups or Users. For example, one Group can collaborate on a specific Folder, or a User can be granted access to a specific File.

Select

When selecting from the Collaborations table, you must specify Id, FileId, FolderId, or GroupId in the WHERE clause.

SELECT * FROM Collaborations WHERE Id = '62696811616';

To retrieve collaborations for a specific file:

SELECT * FROM Collaborations WHERE FileId = '12345678';

If you're authenticated as an administrator with user impersonation permissions, you can query collaborations from multiple user accounts.

SELECT * FROM Collaborations WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query collaborations associated with their account.
• Collaborations shared with multiple users are only returned for one of the users.

Insert

To insert into Collaborations, connect a Folder with a Group or a User and assign a Role to them.

INSERT INTO Collaborations (Role, ItemId, ItemType, AccessibleById, AccessibleByType) VALUES ('admin', '1', 'folder', '2', 'user');

Update

You can only update the Role and Status columns.

UPDATE Collaborations SET Role = 'viewer', Status = 'accepted' WHERE Id = '100';

Delete

Delete a collaboration by specifying its Id:

DELETE FROM Collaborations WHERE Id = '100';

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the collaboration record in Box.
CreatedAt	Datetime	True		The date and time when the collaboration was originally created.
ModifiedAt	Datetime	True		The date and time when the collaboration was last updated.
AcknowledgedAt	Datetime	True		The date and time when the invited collaborator acknowledged or accepted the collaboration.
ExpiresAt	Datetime	False		The date and time when the collaboration expires.
IsAccessOnly	Boolean	True		Indicates whether the collaboration grants access only, without allowing the collaborator to see other collaborations on the item.
CreatedById	String	True	Users.Id	Identifier of the user who created the collaboration invitation.
CreatedByName	String	True		The full name of the user who created the collaboration.
CreatedByLogin	String	True		The login email of the user who created the collaboration.
Status	String	False		The current state of the collaboration, such as pending, accepted, or rejected.
Role	String	False		The permission level granted to the collaborator. Allowed values are editor, viewer, previewer, uploader, previewer uploader, viewer uploader, co-owner, or owner.
ItemId	String	False		Identifier of the file or folder that the collaboration is associated with.
ItemType	String	False		Specifies whether the collaboration is tied to a file or a folder.
ItemName	String	True		The name of the file or folder associated with the collaboration.
AccessibleById	String	False		Identifier of the user or group that has been granted access through the collaboration.
AccessibleByType	String	False		The type of collaborator entity associated with AccessibleById, such as user or group. The allowed values are group, user.
AccessibleByLogin	String	False		The login email of the user or group associated with AccessibleById.
AccessibleByName	String	True		The full name of the user or group granted access through this collaboration.
AccessibleByIsActive	Boolean	True		Indicates whether the user or group granted access through this collaboration is active.
InviteEmail	String	True		The email address of an invited collaborator who is not yet registered in Box.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only to Admin, Co-Admin, and Service Accounts.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
FolderId	String	Identifier of a folder used to filter collaborations related to that folder.
GroupId	String	Identifier of a group used to filter collaborations related to that group.
FileId	String	Identifier of a file used to filter collaborations related to that file.

Contains user comments made on Box files and folders, supporting creation, retrieval, updates, and deletion.

Table Specific Information

Comments are additional information passed on to a File. These Comments can also have child Comments.

Select

When selecting from the Collaborations table, you must specify the Id or FileId in the WHERE clause.

SELECT * FROM Comments WHERE Id = '1925314736840';

If you're authenticated as an administrator with user impersonation permissions, you can query comments from multiple user accounts.

SELECT * FROM Comments WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query comments associated with their account.
• Comments shared with multiple users are only returned for one of the users.

Insert

To insert into Comments, specify the item to insert the Comment into, as well as the Message. Either a Message or a TaggedMessage must be specified, but not both.

INSERT INTO Comments (ItemId, ItemType, Message) VALUES ('1', 'file', 'example comment');

Update

You can only update the message of the Comment.

UPDATE Comments SET Message = 'editor' WHERE Id = '100';

Delete

Delete a comment by specifying its Id:

DELETE FROM Comments WHERE Id = '100';

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the comment.
Message	String	False		The plain text content of the comment. Either this or TaggedMessage must be used, but not both.
TaggedMessage	String	False		The formatted message of the comment that supports tagging users with @[USER_ID:USERNAME], which notifies the tagged user. An invalid tag format results in an error.
ItemId	String	False		Identifier of the item that the comment is attached to, such as a file or another comment.
ItemType	String	False		Specifies the type of item the comment is associated with, which can be a file or another comment. The allowed values are file, comment.
CreatedById	String	True	Users.Id	Identifier of the user who authored the comment.
CreatedByName	String	True		The full name of the user who authored the comment.
CreatedByLogin	String	True		The login email of the user who authored the comment.
CreatedAt	Datetime	True		The date and time when the comment was created.
AsUserId	String	False		Identifier of the user to impersonate for API calls, available only for Admin, Co-Admin, and Service Accounts.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
FileID	String	Identifier of the file the comment is linked to. This field is only for querying and cannot be used when inserting comments.

Represents files stored in Box, including their content, metadata, ownership, and lifecycle information.

Table Specific Information

Select

If you query Files without specifying any condition in the WHERE Clause, only files up to 5 levels deep from the root folder are returned by default. You can change the default depth value in the connection string (for example, DirectoryRetrievalDepth=10).

SELECT * FROM Files;

By default, the search starts from the root directory, identified as directory '0'. To customize the starting directory for your search, specify its Id using the SearchRootId column.

SELECT * FROM Files WHERE SearchRootId = '293533136411';

To search all the Files in your enterprise, query the Files table with the SearchTerms column.

SELECT * FROM Files WHERE SearchTerms = 'untitled';

To search all the Files within a specific folder, query the Files table with a filter on the relevant folder's Id in the ParentId column.

SELECT * FROM Files WHERE ParentId = '12';

Note: When using a ParentId filter, avoid specifying a SearchRootId simultaneously. If both are used, the search will recursively start from the specified SearchRootId instead of the ParentId, which may result in slower performance.

Retrieving File Representations

Box can generate alternate representations of a file, such as extracted text or a PDF. The Representations column returns the file's available representations as a JSON aggregate, including each representation's generation status (its state, and a code when the state is error). This status is a reliable way to identify files that cannot be processed without downloading them. For example, password-encrypted files return a state of 'error' with the code 'error_encrypted_file'.

The generation status is only returned when you request one or more representation types through the RepresentationTypes column, which is sent as the Box X-Rep-Hints request header. The surrounding brackets are optional, so 'extracted_text' and '[extracted_text]' are equivalent. You can request multiple types by chaining them, as in '[extracted_text][pdf]'.

SELECT Id, Name, Representations FROM Files WHERE Id = '123' AND RepresentationTypes = 'extracted_text';

The Representations column is also available when listing the files within a folder.

SELECT Id, Name, Representations FROM Files WHERE ParentId = '12' AND RepresentationTypes = '[extracted_text]';

Note: If RepresentationTypes is not specified, the Representations column returns the available representation types without their generation status.

Update

Any column where ReadOnly=False can be updated.

UPDATE Files SET Description = 'example description', OwnedbyId = '321', ParentId = '12', Name = 'updated file name' WHERE Id = '123';

Delete

Delete a file by specifying its Id. This file is then moved to TrashedItems.

DELETE FROM Files WHERE Id = '100';

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the file.
Name	String	False		The display name of the file.
Sha1	String	False		The SHA-1 checksum of the file, used for content verification and deduplication.
Etag	String	False		Version identifier for the file, used to track changes and manage concurrency.
SequenceId	String	False		An incremental identifier representing the version sequence of the file.
Description	String	False		A user-provided description that explains the file's purpose or contents.
Size	Long	True		The file size in bytes.
CreatedAt	Datetime	True		The date and time when the file was originally created in Box.
ContentCreatedAt	Datetime	True		The date and time when the file content was first created, which may differ from the Box creation date.
CreatedById	String	True	Users.Id	Identifier of the user who created the file.
CreatedByName	String	True		The full name of the user who created the file.
CreatedByLogin	String	True		The login email of the user who created the file.
SharedLink	String	False		A shareable URL that provides access to the file.
ModifiedAt	Datetime	True		The date and time when the file was last updated in Box.
ContentModifiedAt	Datetime	True		The date and time when the file content itself was last modified.
ModifiedById	String	True	Users.Id	Identifier of the user who most recently modified the file.
ModifiedByName	String	True		The full name of the user who most recently modified the file.
ModifiedByLogin	String	True		The login email of the user who most recently modified the file.
OwnedById	String	False	Users.Id	Identifier of the user who owns the file.
OwnedByName	String	False		The full name of the user who owns the file.
OwnedByLogin	String	False		The login email of the user who owns the file.
ParentId	String	False	Folders.Id	Identifier of the folder that contains the file.
ItemStatus	String	False		The current state of the file, such as active or trashed.
TrashedAt	Datetime	True		The date and time when the file was moved to the trash.
PurgedAt	Datetime	True		The date and time when the file was permanently deleted from the trash.
ExpiresAt	Datetime	True		The date and time when the file is scheduled to be automatically deleted.
Path	String	True		The full folder path leading to the file within the Box hierarchy.
LockType	String	True		The type of the lock held on the file, which is always lock.
LockId	String	True		The unique identifier of the lock held on the file.
LockCreatedById	String	True		Identifier of the user who created the lock on the file.
LockCreatedByName	String	True		The full name of the user who created the lock on the file.
LockCreatedByLogin	String	True		The login email of the user who created the lock on the file.
LockCreatedAt	Datetime	True		The date and time when the lock was created on the file.
LockExpiresAt	Datetime	False		The date and time when the lock on the file expires.
LockIsDownloadPrevented	Bool	False		Indicates whether downloading the file is prevented while the lock is held.
Scope	String	True		The scope of the file search, such as limiting results to a user or enterprise.
FileExtension	String	True		The file's extension, such as PDF or DOCX.
ContentTypes	String	True		Specifies which file fields to search against, separated by commas. Possible values include name, file_content, description, comments, and tags.
OwnerUserIDs	String	True		A list of user identifiers separated by a comma, used to restrict searches to files owned by specific users.
AncestorfolderIDs	String	True		A list of folder identifiers separated by a comma, used to restrict searches to files contained within specific folders.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
SearchRootId	String	True		Identifier of the folder to use as the starting point for recursive searches, with '0' representing the root folder.
Representations	String	True		A JSON aggregate of the file's available representations, including each representation's type and generation status (state, and code when present). Requires RepresentationTypes to be set for the status to be populated.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
SearchTerms	String	Keywords used to search for files stored in Box.
RepresentationTypes	String	The representation(s) to evaluate, sent as the Box X-Rep-Hints request header. The surrounding brackets are optional, so 'extracted_text' and '[extracted_text]' are equivalent; already-bracketed or chained values such as '[extracted_text][pdf]' are used as provided. Set this to populate the generation status in the Representations column.

Represents folders in Box, including their structure, contents, and metadata, with full create, update, delete, and query capabilities.

Table Specific Information

Select

If you query Folders without specifying any condition in the WHERE Clause, only files up to 5 levels deep from the root folder are returned by default. You can change the default depth value in the connection string; e.g. DirectoryRetrievalDepth=10.

SELECT * FROM Folders;

By default, the search starts from the root directory, identified as directory '0'. To customize the starting directory for your search, specify its Id using the SearchRootId column.

SELECT * FROM Folders WHERE SearchRootId = '293533136411';

To search all the Folders in your enterprise, query the Folders table with the SearchTerms column.

SELECT * FROM Folders WHERE SearchTerms = 'untitled';

To search all the Folders within a specific folder, query the Folders table with the ParentId column.

SELECT * FROM Folders WHERE ParentId = '12';

Note: When using a ParentId filter, avoid specifying a SearchRootId simultaneously. If both are used, the search will recursively start from the specified SearchRootId instead of the ParentId, which may result in slower performance.

If you're authenticated as an administrator with user impersonation permissions, you can query folders from multiple user accounts:

SELECT * FROM Folders WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query folders associated with their account.
• Folders shared with multiple users are only returned for one of the users.

Insert

You must specify the name of the Folder and also the Id of the parent Folder. Use 0 for the root Folder's ParentId.

INSERT INTO Folders (Name, ParentId, SharedLink, Description, Access, OwnedById) VALUES ('new folder', '0', 'http://sharedlink.com', 'example desc', 'open', '123');

Update

Any column where ReadOnly=False can be updated.

UPDATE Folders SET Description = 'example description', Access = 'open', SharedLink = 'http://sharedlink.com', OwnedById = '321', ParentId = '12', Name = 'no longer new folder'  WHERE Id = '123';

Delete

Delete a folder by specifying its Id:

DELETE FROM Folders WHERE Id = '123';

Columns

Name	Type	ReadOnly	References	Description
SearchTerms	String	True		Keywords used to search for folders stored in Box.
Id [KEY]	String	True		Unique identifier assigned to the folder.
Name	String	False		The display name of the folder.
Etag	String	True		Version identifier for the folder, used to detect changes and manage concurrency.
SequenceId	String	True		An incremental identifier representing the version sequence of the folder.
Description	String	False		A user-provided description that explains the purpose or contents of the folder.
Access	String	False		The email addresses of users who have been granted access to the folder. The allowed values are open, collaborators.
Size	Integer	True		The total size of the folder contents, measured in bytes.
CreatedAt	Datetime	True		The date and time when the folder was originally created in Box.
ModifiedAt	Datetime	True		The date and time when the folder's metadata or contents were last updated.
TrashedAt	Datetime	True		The date and time when the folder was moved to the trash.
PurgedAt	Datetime	True		The date and time when the folder was permanently deleted from the trash.
ContentCreatedAt	Datetime	True		The date and time when the first content item inside the folder was created.
ContentModifiedAt	Datetime	True		The date and time when the most recent content inside the folder was modified.
CreatedBy	String	True	Users.Id	Identifier of the user who created the folder.
CreatedByName	String	True		The full name of the user who created the folder.
CreatedByLogin	String	True		The login email of the user who created the folder.
SharedLink	String	False		A shareable URL that provides access to the folder.
ModifiedBy	String	True	Users.Id	Identifier of the user who last modified the folder.
ModifiedByName	String	True		The full name of the user who last modified the folder.
ModifiedByLogin	String	True		The login email of the user who last modified the folder.
OwnedById	String	False	Users.Id	Identifier of the user who owns the folder.
OwnedByName	String	True		The full name of the user who owns the folder.
OwnedByLogin	String	True		The login email of the user who owns the folder.
ParentId	String	False	Folders.Id	Identifier of the parent folder that contains this folder.
ItemStatus	String	True		The current state of the folder, such as active or trashed.
Path	String	True		The full folder path leading to the folder within the Box hierarchy.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
SearchRootId	String	True		Identifier of the folder to use as the starting point for recursive searches, with '0' representing the root folder.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
Scope	String	The scope of the folder search, such as limiting results to a user or enterprise.
ContentTypes	String	Specifies which folder-related fields to search against, separated by commas. Possible values include name, file_content, description, comments, and tags.
OwnerUserIDs	String	A list of user identifiers separated by a comma, used to restrict searches to folders owned by specific users.
AncestorfolderIDs	String	A list of folder identifiers separated by a comma, used to restrict searches to folders contained within specific parent folders.

Manages groups of users in Box for easier collaboration and permission assignment across files and folders.

Table Specific Information

Select

You can retrieve all the Groups or filter them by Id.

SELECT * FROM Groups;

If you're authenticated as an administrator with user impersonation permissions, you can query groups from multiple user accounts:

SELECT * FROM Groups WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query groups associated with their account.
• Groups shared with multiple users are only returned for one of the users.

Insert

Only the Name column is required to create a Group.

INSERT INTO Groups (Name, Provenance, MemberViewabilityLevel) VALUES ('new group', 'google', 'admins_only');

Update

Any column where ReadOnly=False can be updated.

UPDATE Groups SET InvitabilityLevel = 'admins_and_members' WHERE Id = '111';

Delete

Delete a group by specifying its Id:

DELETE FROM Groups WHERE Id = '111';

Columns

Name	Type	ReadOnly	References	Description
ID [KEY]	String	True		Unique identifier assigned to the group.
Name	String	False		The display name of the group.
Provenance	String	False		Tracks the external system or source from which the group originated, often used for integration purposes.
ExternalSyncIdentifier	String	False		An identifier used to map the group to an external system when groups are synchronized from outside sources.
Description	String	False		A user-provided description that explains the purpose or membership of the group.
InvitabilityLevel	String	False		Defines who is allowed to invite this group to collaborate on folders, such as admins only or all group members.
MemberViewabilityLevel	String	False		Defines who is allowed to view the membership of the group, such as only admins or all collaborators.
CreatedAt	Datetime	True		The date and time when the group was originally created. An Id must be specified to retrieve this field.
ModifiedAt	Datetime	True		The date and time when the group's details were last updated. An Id must be specified to retrieve this field.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Stores legal hold policies created by an enterprise to preserve content for compliance or litigation purposes.

Table Specific Information

Note: The Legal Hold feature must be enabled for a Box enterprise account to query this table. To add this feature, please visit Box Governance and Compliance.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		Unique identifier assigned to the legal hold policy.
AssignmentFileCount	Integer	True		The number of files currently under this legal hold policy.
AssignmentFileVersionCount	Integer	True		The number of file versions assigned to this legal hold policy.
AssignmentFolderCount	Integer	True		The number of folders covered by this legal hold policy.
AssignmentUserCount	Integer	True		The number of users assigned to this legal hold policy.
CreatedAt	Datetime	True		The date and time when the legal hold policy was created.
CreatedById	String	True		Identifier of the user who created the legal hold policy.
CreatedByLogin	String	True		The login email of the user who created the legal hold policy.
CreatedByName	String	True		The full name of the user who created the legal hold policy.
CreatedByType	String	True		Specifies the type of user or account that created the policy, such as a user or service account.
DeletedAt	Datetime	True		The date and time when the legal hold policy was deleted, if applicable.
Description	String	False		A user-provided description of the legal hold policy and its intended purpose.
FilterEndedAt	Datetime	False		The end date and time of the policy's filter criteria, used to define the retention window.
FilterStartedAt	Datetime	False		The start date and time of the policy's filter criteria, used to define the retention window.
ModifiedAt	Datetime	True		The date and time when the legal hold policy was last updated.
PolicyName	String	False		The display name of the legal hold policy.
ReleaseNotes	String	False		Notes provided when the policy was released, describing the reason or details of the release.
Status	String	True		The current state of the policy, such as active or released.
Type	String	True		Specifies the type of policy, such as legal hold or another retention category.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
IsOngoing	Boolean	False		Indicates whether the policy should continue applying to new assignments created after its initialization.

Defines user memberships in groups, linking users to groups and managing their access within Box.

Table Specific Information

Memberships are the relations between a User and a Group.

Select

When selecting from the Memberships table, you must specify Id, GroupId, or UserId in the WHERE clause.

SELECT * FROM Memberships WHERE Id = '24201762883';

If you're authenticated as an administrator with user impersonation permissions, you can query memberships from multiple user accounts. For example:

SELECT * FROM Memberships WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query memberships associated with their account.
• Memberships shared with multiple users are only returned for one of the users.

Insert

To insert into Memberships, connect a Group (GroupId) with a User (UserId).

INSERT INTO Memberships (GroupId, UserId) VALUES (1001, 123);

Update

You can only update the role of Memberships. The default role is 'memberships' with the option for 'admin'.

UPDATE Memberships SET Role = 'admin' WHERE Id = '100';

Delete

Delete a membership by specifying its Id:

DELETE FROM Memberships  WHERE Id = '100'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the membership record.
Role	String	False		The role granted to the user within the group, such as member or admin.
CreatedAt	Datetime	True		The date and time when the membership was created. Requires an Id to retrieve this field.
ModifiedAt	Datetime	True		The date and time when the membership was last updated. Requires an Id to retrieve this field.
UserName	String	True		The full name of the user who is part of the membership.
UserLogin	String	True		The login email of the user who is part of the membership.
UserId	String	False	Users.Id	Identifier of the user associated with the membership.
GroupName	String	False		The display name of the group that the membership belongs to.
GroupId	String	False	Groups.Id	Identifier of the group that the membership is associated with.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Represents assignments of tasks within Box, linking a task to the users responsible for completing it.

Table Specific Information

Task Assignments are Tasks assigned to a specific User.

Select

When selecting from the Task Assignments table, you must specify Id or TaskId in the WHERE clause.

SELECT * FROM TaskAssignments WHERE Id = '35991959268';

If you're authenticated as an administrator with user impersonation permissions, you can query task assignments from multiple user accounts. For example:

SELECT * FROM TaskAssignments WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query task assignments associated with their account.
• Task assignments shared with multiple users are only returned for one of the users.

Insert

To insert into Task Assignments, connect a Task (TaskId) with a User (AssignedToId).

INSERT INTO TaskAssignments (TaskId, AssignedToId) VALUES (1001, 123);

Update

You can only update the Message of the Task Assignment or its ResolutionState.

UPDATE TaskAssignments SET Message = 'x', ResolutionState = 'completed' WHERE Id = '111';

Delete

Delete task assignments by specifying its Id:

DELETE FROM TaskAssignments WHERE Id = '111';

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the task assignment.
TaskId	String	False		Identifier of the task that is being assigned.
AssignedToId	String	False	Users.Id	Identifier of the user who has been assigned to complete the task.
AssignedToName	String	False		The full name of the user who has been assigned the task.
AssignedToLogin	String	False		The login email of the user who has been assigned the task.
ItemType	String	False		Specifies the type of item associated with the task assignment, such as a file or folder.
ItemId	String	False		Identifier of the item associated with the task assignment.
ItemName	String	False		The display name of the item associated with the task assignment.
Message	String	False		The note or instructions attached to the task assignment.
AssignedById	String	False	Users.Id	Identifier of the user who created and assigned the task.
AssignedByName	String	False		The full name of the user who created and assigned the task.
AssignedByLogin	String	False		The login email of the user who created and assigned the task.
AssignedAt	Datetime	True		The date and time when the task was assigned.
RemindedAt	Datetime	True		The date and time when a reminder for the task was sent.
CompletedAt	Datetime	True		The date and time when the task was marked as completed.
ResolutionState	String	False		The current status of the task assignment, indicating whether it has been addressed or remains unresolved. The allowed values are completed, incomplete, approved, rejected.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Stores tasks created in Box for files and folders, supporting assignment, due dates, and completion tracking.

Table Specific Information

Tasks are jobs that are waiting to be assigned to someone. These tasks are related to an item, which generally is a File.

Select

When selecting from the Tasks table, you must specify Id or ItemId in the WHERE clause.

SELECT * FROM Tasks WHERE ItemId = '1925314736840';

If you're authenticated as an administrator with user impersonation permissions, you can query tasks from multiple user accounts.

SELECT * FROM Tasks WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query tasks associated with their account.
• Tasks shared with multiple users are only returned for one of the users.

Insert

ItemId is required to insert into Tasks table. Currently, Box API supports only file as type of the item the task is for.

INSERT INTO Tasks (ItemId, ItemType, Action, Message) VALUES (1001, 'file', 'review', 'message');

Update

The Message is required to perform an UPDATE to a specific task. Also, the Action and DueAt columns can be updated as well. The Action column is only able to take a 'review' value.

UPDATE Tasks SET Message = 'updated message', Action = 'review', DueAt = '2016-05-14' WHERE ID = '100'; 

Delete

Delete a task by specifying its Id:

DELETE FROM Tasks WHERE Id = '100';

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the task.
DueAt	Datetime	False		The date and time when the task is due for completion.
ItemId	String	False		Identifier of the item the task is associated with, such as a file or folder.
ItemType	String	False		Specifies the type of item the task is linked to, such as a file or folder.
Action	String	False		The required action for the task assignee, such as reviewing, approving, or completing an item.
Message	String	False		An optional message or instruction included with the task to provide context for the assignee.
CreatedAt	Datetime	True		The date and time when the task was created.
CreatedById	String	True	Users.Id	Identifier of the user who created the task.
CreatedByName	String	True		The full name of the user who created the task.
CreatedByLogin	String	True		The login email of the user who created the task.
IsCompleted	Boolean	False		Indicates whether the task has been marked as completed.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Contains items moved to the trash, allowing users to view, query, or permanently delete trashed files and folders.

Table Specific Information

Trashed Items are Files and Folders that have been deleted. This table supports only two operations: Select and Delete. Deleting from this table permanently deletes the File and is not recoverable.

Select

To search in the Trashed Items table for a specific item, specify the type of item (File, Folder, or WEB_LINK).

SELECT * FROM TrashedItems WHERE SearchTerms = 'Untitled' AND Type = 'file';

To query a single-deleted Folder, you can execute a query like the following:

SELECT * FROM TrashedItems WHERE Id = '123' AND Type = 'folder';

To query a single-deleted WEB_LINK, you can execute a query like the following:

SELECT * FROM TrashedItems WHERE Id = '123' AND Type = 'web_link';

Delete

Delete trashed items by specifying the Id and the Type of item.

DELETE FROM TrashedItems WHERE Id = 'abc123' AND Type = 'file';

Columns

Name	Type	ReadOnly	References	Description
SearchTerms	String	False		A query string used to search across trashed items.
Id [KEY]	String	False		The unique identifier of the trashed item.
Name	String	False		The name of the trashed item.
Type	String	False		The type of trashed item, such as file, folder, or web_link.
Sha1	String	False		The SHA-1 hash of the trashed item for file integrity verification.
Etag	String	False		The entity tag of the trashed item used for version control.
SequenceId	String	False		The sequence Id of the trashed item, used for internal ordering and version tracking.
Description	String	False		The description or notes about the trashed item.
Size	Int64	False		The size of the trashed item in bytes.
CreatedAt	Datetime	False		The date and time when the trashed item was originally created.
ModifiedAt	Datetime	False		The date and time when the trashed item was last modified.
TrashedAt	Datetime	False		The date and time when the item was moved to the trash.
PurgedAt	Datetime	False		The date and time when the item was permanently deleted from the trash.
ContentCreatedAt	Datetime	False		The date and time when the content of the item was originally created.
ContentModifiedAt	Datetime	False		The date and time when the content of the item was last modified.
CreatedById	String	False	Users.Id	The Id of the user who created the trashed item.
CreatedByName	String	False		The name of the user who created the trashed item.
CreatedByLogin	String	False		The login of the user who created the trashed item.
SharedLink	String	False		The shared link associated with the trashed item, if one exists.
ModifiedBy	String	False	Users.Id	The Id of the user who last modified the trashed item.
ModifiedByName	String	False		The name of the user who last modified the trashed item.
ModifiedByLogin	String	False		The login of the user who last modified the trashed item.
OwnedById	String	False	Users.Id	The Id of the user who owns the trashed item.
OwnedByName	String	False		The name of the user who owns the trashed item.
OwnedByLogin	String	False		The login of the user who owns the trashed item.
ParentId	String	False		The Id of the parent folder containing the trashed item.
ItemStatus	String	False		The current status of the trashed item, such as active, trashed, or purged.
AsUserId	String	False		The Id of the user you want to impersonate. Only works with Admin, Co-Admin, and Service Accounts.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
Scope	String	Defines the scope of the search, such as enterprise or user-specific.
FileExtension	String	The file extension of the trashed item, if applicable.
SearchContent	String	Specifies which parts of the item to search (such as name, description, or content), separated by commas.

Stores user account details in Box, supporting creation, management, and retrieval of users across the enterprise.

Table Specific Information

Note: Authenticated users must have permission to look up users in the entire Box enterprise.

Select

You can retrieve information about the current User, a User in your enterprise specified by Id, or all the Users in the enterprise.

SELECT * FROM Users;

You can also retrieve the information of any User who is in your enterprise:

SELECT * FROM Users WHERE Id = '1';

To retrieve the details of the currently authenticated user use the FetchOnlyAuthenticatedUser pseudo-column:

SELECT Id, Login, Name, SpaceUsed, SpaceAmount FROM Users WHERE FetchOnlyAuthenticatedUser = TRUE

Note: When set to TRUE, it calls the Box

/users/me

endpoint and returns a single row for the authenticated user.

If you're authenticated as an administrator with user impersonation permissions, you can query users from multiple user accounts:

SELECT * FROM Users WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query users associated with their account.
• Users shared with multiple users are only returned for one of the users.

Insert

You can invite a User to your enterprise. The following query will send an invitation email to join your enterprise:

INSERT INTO Users (Login, Name, Role) VALUES ('[email protected]', 'Username', 'coadmin');

Update

Any column where ReadOnly=False can be updated.

UPDATE Users SET Name = 'Test', Role = 'user', IsExemptFromDeviceLimits = false WHERE Id = '111';

Delete

Delete users by specifying its Id:

DELETE FROM Users WHERE Id = '111'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier assigned to the user. When querying, this can be set to the value 'me' to return details for the currently authenticated user.
Login	String	False		The login email address of the user.
Name	String	False		The full display name of the user.
EnterpriseId	String	False		Identifier of the enterprise the user belongs to. Can be set to null to disassociate the user from the enterprise.
Role	String	False		The user's role within the enterprise, such as admin, co-admin, or user.
Language	String	False		The preferred language setting for the user interface.
IsPlatformAccessOnly	Boolean	False		Indicates whether the account is a Box app user without full standard access.
IsSyncEnabled	Boolean	False		Indicates whether the user is allowed to use Box Sync for offline file access.
JobTitle	String	False		The job title provided for the user.
Phone	String	False		The phone number associated with the user.
Address	String	False		The mailing address of the user.
SpaceAmount	Long	False		The total storage space allocated to the user, measured in bytes.
SpaceUsed	Long	False		The amount of storage space the user has consumed, measured in bytes.
MaxUploadSize	Long	False		The maximum file size the user is allowed to upload, measured in bytes.
TrackingCodes	String	False		Custom tracking codes associated with the user, separated by commas.
CanSeeManagedUsers	Boolean	False		Indicates whether the user has permission to view other managed users in the enterprise.
TimeZone	String	False		The preferred time zone setting for the user.
IsExemptFromDeviceLimits	Boolean	False		Indicates whether the user is exempt from restrictions on the number of devices linked to their account.
IsExemptFromLoginVerification	Boolean	False		Indicates whether the user is exempt from login verification requirements, such as two-step authentication.
Status	String	False		The current status of the user account, such as active, inactive, or deleted.
IsPasswordResetRequired	Boolean	False		Indicates whether the user must reset their password on the next login.
CreatedAt	Datetime	True		The date and time when the user account was created.
ModifiedAt	Datetime	True		The date and time when the user account was last updated.
AsUserId	String	False		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Box Views

Name	Description
Collections	List Collections in Box.
Collection_MyFavorites	Each Collection_* view exposes the files and folders that belong to a specific Box collection, and this schema documents the columns common to all of them.
FileMetadataInstances	Retrieves metadata applied to specific files, showing key-value fields associated with metadata templates.
FileVersions	Query the available file versions in the box.
MetadataTemplates	View of Box metadata templates and their fields. One row per (template, field), with template-level columns repeated. Use the CreateMetadataTemplate, ModifyMetadataTemplate, and DeleteMetadataTemplate stored procedures to add, modify, or delete templates and fields.
RecentItems	List RecentItems in Box.
SharedItems	Query the available shared items in Box.
SignRequests	List all Sign Requests.
SignTemplates	Lists Box Sign templates created by users, including template metadata, signer configuration, and document references.
UserEvents	Query the available Events in Box.

List Collections in Box.

Columns

Name	Type	References	Description
Id	String		The unique identifier for this collection.
Name	String		The name of the collection.
Type	String		The type of the collection.
CollectionType	String		The type of the collection. This is used to determine the proper visual treatment for collections.

Each Collection_* view exposes the files and folders that belong to a specific Box collection, and this schema documents the columns common to all of them.

Table Specific Information

When ExposeCollectionViews is set to true, a view prefixed with Collection_ is generated for each of your Box collections, and each view exposes the metadata of the files and folders that belong to that collection using this common schema.

Select

Query the files and folders in a specific collection by selecting from its generated view.

      SELECT * FROM Collection_MyFavorites;
    

To expose collection-specific custom metadata fields as additional columns on the generated views, set IncludeCustomFields to true. You can also use AsUserId to expose the collections of a specific user.

Columns

Name	Type	References	Description
Id [KEY]	String		Unique identifier assigned to the item in the collection.
Name	String		The display name of the item.
Etag	String		Version identifier for the item, used to track changes and manage concurrency.
SequenceId	String		An incremental identifier representing the version sequence of the item.
Description	String		A user-provided description that explains the item's purpose or contents.
Size	Long		The item size in bytes.
CreatedAt	Datetime		The date and time when the item was originally created in Box.
ContentCreatedAt	Datetime		The date and time when the item content was first created, which may differ from the Box creation date.
CreatedById	String	Users.Id	Identifier of the user who created the item.
CreatedByName	String		The full name of the user who created the item.
CreatedByLogin	String		The login email of the user who created the item.
SharedLink	String		A shareable URL that provides access to the item.
ModifiedAt	Datetime		The date and time when the item was last updated in Box.
ContentModifiedAt	Datetime		The date and time when the item content itself was last modified.
ModifiedById	String	Users.Id	Identifier of the user who most recently modified the item.
ModifiedByName	String		The full name of the user who most recently modified the item.
ModifiedByLogin	String		The login email of the user who most recently modified the item.
OwnedById	String	Users.Id	Identifier of the user who owns the item.
OwnedByName	String		The full name of the user who owns the item.
OwnedByLogin	String		The login email of the user who owns the item.
ParentId	String	Folders.Id	Identifier of the folder that contains the item.
ItemStatus	String		The current state of the item, such as active or trashed.
TrashedAt	Datetime		The date and time when the item was moved to the trash.
PurgedAt	Datetime		The date and time when the item is scheduled to be permanently deleted from the trash.
Path	String		The tree of folders that contains the item, starting at the root.
Type	String		The type of the item, which is always file, folder, or web_link.
AsUserId	String		Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
SearchRootId	String		Identifier of the folder to use as the starting point for recursive searches, with '0' representing the root folder.

Retrieves metadata applied to specific files, showing key-value fields associated with metadata templates.

Select

To select from the File Metadata Instances view, you must specify FileID, or FileID and Scope and Template in the WHERE clause. For example:

SELECT * FROM FileMetadataInstances WHERE FileId = '1723610396614';

Columns

Name	Type	References	Description
Id [KEY]	String		A universally unique identifier (UUID) assigned to the metadata instance.
FileId [KEY]	String	Files.Id	Identifier of the file to which the metadata instance is attached.
Template	String		The name of the metadata template applied to the file.
Parent	String		Identifier of the parent item the metadata instance is attached to, formatted as {type}_{id} to combine item type and identifier.
Scope	String		Specifies the scope in which the metadata template is applied. This can be enterprise_{enterprise_id} for enterprise-specific templates or global for templates available across all enterprises.
Version	String		The version number of the metadata instance, starting at 0 and incrementing each time a user-defined property is updated.
CanEdit	String		Indicates whether the current user has permission to edit this metadata instance.
Type	String		The object type identifier for the metadata instance.
TypeVersion	String		The version of the metadata template definition that was last applied to this instance.
UserDefinedFieldName [KEY]	String		The name of a user-defined field included in the metadata template.
UserDefinedFieldValue	String		The value assigned to a user-defined field in the metadata template.

Query the available file versions in the box.

Table Specific Information

File Versions contains information about older versions of a file. This table supports only the SELECT operation.

Select

When selecting from the File Versions view, you must specify FileId, or FileId and Id in the WHERE clause.

SELECT * FROM FileVersions WHERE FileId = '1926408231807';

If you're authenticated as an administrator with user impersonation permissions, you can query file versions from multiple user accounts. For example:

SELECT * FROM FileVersions WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query file versions associated with their account.
• File versions shared with multiple users are only returned for one of the users.

Columns

Name	Type	References	Description
Id [KEY]	String		The ID of the file version.
FileId	String	Files.Id	The ID of the file.
CreatedAt	Datetime		The date the content of the file was created at.
ModifiedAt	Datetime		The date the content of the file was modified at.
ModifiedById	Integer		The Id of the user that last modified the file.
ModifiedByName	String		The Name of the user that last modified the file.
ModifiedByLogin	String		The Login of the user that last modified the file.
ModifiedByType	String		The Type of the user that last modified the file.
Name	String		The name of the file.
PurgedAt	Datetime		The date the content of the file was purged at.
RestoredAt	Datetime		The date the content of the file was modified at.
RestoredById	Integer		The Id of the user that last modified the file.
RestoredByLogin	String		The Name of the user that last modified the file.
RestoredByName	String		The Login of the user that last modified the file.
RestoredByType	String		The Type of the user that last modified the file.
Sha1	String		The SHA-1 encryption of the file version.
Size	Integer		The size of the file version.
TrashedAt	Datetime		The date the file version was trashed at.
TrashedById	Integer		The Id of the user that trashed the file.
TrashedByLogin	String		The Name of the user that trashed the file.
TrashedByName	String		The Login of the user that trashed the file.
TrashedByType	String		The Type of the user that trashed the file.
Type	String		Type of file versions.
AsUserId	String		The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.

View of Box metadata templates and their fields. One row per (template, field), with template-level columns repeated. Use the CreateMetadataTemplate, ModifyMetadataTemplate, and DeleteMetadataTemplate stored procedures to add, modify, or delete templates and fields.

Table Specific Information

Select

You can retrieve information about the metadata templates available in your enterprise.

SELECT * FROM MetadataTemplates;

You can also retrieve a single template by its identifier:

SELECT * FROM MetadataTemplates WHERE Id = 'e93746cc-03f6-4dee-ab45-01834989950c';

Modifying templates

MetadataTemplates is exposed as a read-only view. To add, modify, or delete a template, call one of the dedicated stored procedures:

• 
  

  CreateMetadataTemplate

  creates a new template and, optionally, seeds it with one or more fields in a single call.
• 
  

  ModifyMetadataTemplate

  modifies the schema of an existing template. The change to apply (add a field, rename an option, reorder fields, and so on) is selected through the Op input.
• 
  

  DeleteMetadataTemplate

  deletes a template and all of its fields.

Columns

Name	Type	References	Description
Id [KEY]	String		Unique identifier assigned to the metadata template.
TemplateKey	String		A unique key that identifies the metadata template within the enterprise. This key is unique inside a single enterprise but might differ across enterprises.
CopyInstanceOnItemCopy	Boolean		Indicates whether metadata applied to a file or folder should be copied when the item itself is copied.
DisplayName	String		The user-friendly name of the metadata template as shown in the Box web and mobile apps.
FieldsId [KEY]	String		Unique identifier assigned to a specific field within the metadata template.
FieldsKey	String		A unique key for the field within its metadata template, required to distinguish it from other fields.
FieldsDisplayName	String		The display name of the field as shown to users in the Box web and mobile apps.
FieldsType	String		The type of the metadata field, such as string, enum, or multiSelect.
FieldsDescription	String		A description of the metadata field's purpose or usage.
FieldsisHidden	Boolean		Indicates whether the field is hidden in the user interface and can only be set programmatically through the API.
FieldsOptions	String		A list of allowed options for the field, used with enum and multiSelect field types.
TemplateisHidden	Boolean		Indicates whether the template is visible in the Box web application or restricted to API usage only.
Scope	String		Defines the scope of the template, such as enterprise-wide or global.
Type	String		The object type for the template. This value is always metadata_template.

List RecentItems in Box.

Columns

Name	Type	References	Description
Type	String		The type of the recent item.
InteractedAt	Datetime		The time of the most recent interaction.
InteractionSharedLink	String		If the item was accessed through a shared link it will appear here, otherwise this will be null.
InteractionType	String		The most recent type of access the user performed on the item.
ItemId	String		The unique identifier for the recent item.

Query the available shared items in Box.

Table Specific Information

Shared Items are Files that are open to public viewing by using a URL. These files can also be password protected. Anyone who has access to this URL and password can read/write to the file.

Select

To select from the Shared Items view, specify the SharedLink column that points to the item.

SELECT * FROM SharedItems WHERE SharedLink = 'https://app.box.co/s/abcdef123456';

If the SharedItem has a password, specify the password as well.

SELECT * FROM SharedItems WHERE SharedLink = 'www.url.com' AND SharedLinkPassword = 'password';

If you're authenticated as an administrator with user impersonation permissions, you can query shared items from multiple user accounts. For example:

SELECT * FROM SharedItems WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query shared items associated with their account.
• Shared items shared with multiple users are only returned for one of the users.

Columns

Name	Type	References	Description
Name	String		The name of the shared item.
ItemId [KEY]	String		The Id of the shared item.
ItemType	String		The item type that the shared item points to.
CreatedAt	String		The date the shared item was created at.
Description	String		The description of the of the shared item.
ModifiedAt	String		The date the shared item was modified at.
OwnedById	String	Users.Id	The id of owner of the shared item.
OwnedByName	String		The name of owner of the shared item.
OwnedByLogin	String		The login of owner of the shared item.
ParentId	String		The id of the parent of the shared item.
ParentName	String		The Name of the parent of the shared item.
Access	String		The availability of the shared item.
DownloadCount	Integer		The number of times the item was downloaded
DownloadUrl	String		The url to download the item from.
IsPasswordEnabled	String		If you need to specify a password to download the file or not.
CanDownload	Boolean		If the item is available to download or not.
CanPreview	Boolean		If the item is available to preview or not.
PreviewCount	Integer		The number of times the item was previewed.
Url	String		The preview url of the item.
Size	Long		The size of the item.
AsUserId	String		The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
SharedLink	String		The shared link to query.
SharedLinkPassword	String		The password of shared link.

List all Sign Requests.

Columns

Name	Type	References	Description
SignRequestAggregate	String		A JSON Aggregate representing the SignRequest item.
AsUserId	String		The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.

Lists Box Sign templates created by users, including template metadata, signer configuration, and document references.

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier of the Sign template.
Name	String		The name of the Sign template.
EmailSubject	String		The subject line used in signature request emails generated from this template.
EmailMessage	String		The message body included in signature request emails generated from this template.
DaysValid	Integer		The number of days after which signature requests created from this template automatically expire.
AreFieldsLocked	Boolean		Indicates whether the template input fields are editable.
AreOptionsLocked	Boolean		Indicates whether the template document options are editable.
AreRecipientsLocked	Boolean		Indicates whether the template signers are editable.
AreEmailSettingsLocked	Boolean		Indicates whether the template email settings are editable.
AreFilesLocked	Boolean		Indicates whether the template files are editable, including deleting or renaming template files.
ParentFolderId	String		The unique identifier of the destination folder for final signed documents and signing logs.
ParentFolderName	String		The name of the destination folder for final signed documents and signing logs.
ReadySignLinkUrl	String		The URL of the ready-sign link that can be shared publicly for signing.
ReadySignLinkName	String		The name associated with the ready-sign link.
ReadySignLinkIsActive	Boolean		Whether the ready-sign link is currently enabled.
CustomBrandingCompanyName	String		The company name used in custom branding for notifications and signature requests.
CustomBrandingColor	String		The custom branding color in hex format.
AsUserId	String		The Id of the user to impersonate. Only works with Admin, Co-Admin, and Service Accounts.

Query the available Events in Box.

Table Specific Information

User events are logs that are saved after a change in a user's account.

Select

Query all the current user's events:

SELECT * FROM UserEvents;

If you're authenticated as an administrator with user impersonation permissions, you can query user events from multiple user accounts:

SELECT * FROM UserEvents WHERE AsUserId IN (SELECT Id FROM Users);

Note: User impersonation has the following limitations:

• The authenticated user cannot be impersonated unless that user is a service account.
• If the authenticated user is a standard user account, they can only query user events associated with their account.
• User events shared with multiple users are only returned for one of the users.

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the event.
SourceId	String		The Id of the source that triggered the event.
SourceType	String		The type of the source that triggered the event.
CreatedById	String	Users.Id	The Id of the user that created the event.
CreatedByName	String		The Name of the user that created the event.
CreatedByLogin	String		The Login of the user that created the event.
CreatedAt	Datetime		The date the event was created at. This can only be used when requesting the events with a StreamType of admin_logs
RecordedAt	Datetime		The date the event was recorded at.
SessionId	String		The Id of the session of the event.
EventType	String		The type of the event.
ClassificationName	String		The classification name of the event.
AdditionalDetails	String		This object provides additional information about the event if available. This can include how a user performed an event as well as additional information to correlate an event to external KeySafe logs. Not all events have an additional_details object. This object is only available in the Enterprise Events.
AsUserId	String		The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.
AsUserId	String		The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Box.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Box, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Box Stored Procedures

Name	Description
AddMetadataTemplatestoFile	Applies a predefined metadata template to a file, creating a structured set of metadata fields for that file.
CopyFile	Creates a copy of an existing file in Box within the same folder or a different destination folder.
CopyFolder	Creates a duplicate of an entire folder, including its subfolders and files, in another location in Box.
CreateMetadataTemplate	Creates a new Box metadata template, optionally seeded with one or more fields.
CreateSignRequest	Creates a new electronic signature request for documents stored in Box using Box Sign.
DeleteExternalUsers	Delete external users from current user enterprise. This will remove each external user from all invited collaborations within the current enterprise.
DeleteFile	Permanently deletes a specified file from Box.
DeleteMetadataTemplate	Deletes a Box metadata template and all of its fields.
DownloadFile	Downloads the contents of a file from Box to a local system or application.
EmptyTrash	Permanently deletes all items currently in the trash for the authenticated user or enterprise.
LockFile	Locks a file in Box to prevent other users from editing or uploading new versions while the lock is active.
ModifyMetadataTemplate	Modifies a Box metadata template's schema.
RestoreItem	Restores a previously deleted file or folder from the trash back to its original location.
RevokeShareLink	Revokes the shared link for a file, folder, or bookmark, removing external access.
Search	Performs a global search across Box to find files, folders, and other items matching a search term.
ShareAnItem	Creates or updates a shared link for a file, folder, or bookmark, enabling controlled external access.
UnlockFile	Releases a file lock so that other users can edit or upload a new version.
UpdateFile	Updates the content or metadata of an existing file in Box.
UploadFile	Uploads a new file into a specified Box folder.
UploadFileVersion	Uploads a new version of an existing file, maintaining version history in Box.

Applies a predefined metadata template to a file, creating a structured set of metadata fields for that file.

Input

Name	Type	Description
FileId	String	Unique identifier of the file to which the metadata template is applied.
TemplateKey	String	The key name of the metadata template that defines the fields to be applied.
Scope	String	The scope of the metadata template, such as enterprise or global.
TemplateValues	String	Optional field that specifies the values for the metadata template. Values must be separated by a semicolon(for example, 'DepartmentName=TestDepartment; DepartmentId=101'.)
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	Bool	Indicates whether the metadata template was successfully added to the file.

Creates a copy of an existing file in Box within the same folder or a different destination folder.

Input

Name	Type	Description
FileID	String	Identifier of the file to be copied.
DestinationFolderId	String	Identifier of the destination folder where the file is copied.
FileVersionID	String	Identifier of a specific file version to copy. If not provided, the most recent version of the file is copied.
NewName	String	Optional new name to assign to the copied file. If omitted, the original name is retained.
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file copy operation completed successfully.
ID	String	Unique identifier assigned to the newly copied file.

Creates a duplicate of an entire folder, including its subfolders and files, in another location in Box.

Input

Name	Type	Description
FolderID	String	Identifier of the folder to be copied.
DestinationFolderId	String	Identifier of the destination folder where the folder is copied.
NewName	String	Optional new name to assign to the copied folder. If omitted, the original name is retained.
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the folder copy operation completed successfully.
ID	String	Unique identifier assigned to the newly copied folder.

Creates a new Box metadata template, optionally seeded with one or more fields.

Execute

Creates a new metadata template, optionally seeded with one or more fields in a single API call.

You can supply the fields payload in two ways: as a JSON array on the FieldsAggregate input, or by populating the MetadataFields#TEMP temporary table and passing its name to the same input.

JSON FieldsAggregate

The simplest call provides only the required inputs:

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_minimal',
DisplayName = 'QA Minimal';

Template-level flags such as TemplateIsHidden and CopyInstanceOnItemCopy can be supplied alongside the required inputs:

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_flags',
DisplayName = 'QA Flags',
TemplateIsHidden = 'true',
CopyInstanceOnItemCopy = 'true';

A single field can be seeded by passing a JSON array on FieldsAggregate:

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_one_field',
DisplayName = 'QA One Field',
FieldsAggregate = '[{"key":"f1","type":"string","displayName":"Field 1"}]';

Multiple fields, including an enum with options, in a single call:

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_batch',
DisplayName = 'QA Batch',
FieldsAggregate = '[
  {"key":"name","type":"string","displayName":"Name"},
  {"key":"sev","type":"enum","displayName":"Severity",
   "options":[{"key":"low"},{"key":"medium"},{"key":"high"}]}
]';

A taxonomy field is configured through additional keys (taxonomyKey, taxonomyId, namespace) and the nested optionsRules object:

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_taxonomy_json',
DisplayName = 'QA Taxonomy (JSON)',
FieldsAggregate = '[
  {"key":"category","type":"taxonomy","displayName":"Category",
   "taxonomyKey":"category-tax","taxonomyId":"tax-12345","namespace":"enterprise",
   "optionsRules":{"multiSelect":false,"selectableLevels":[1,2,3]}}
]';

MetadataFields#TEMP aggregate

The second way is to populate the MetadataFields#TEMP temporary table and pass its name to FieldsAggregate. Each row in the temporary table becomes one field on the new template. The accepted columns are listed in the schema below.

INSERT INTO MetadataFields#TEMP (Key, Type, DisplayName) VALUES ('f1', 'string', 'Field 1');
INSERT INTO MetadataFields#TEMP (Key, Type, DisplayName, Options) VALUES ('sev', 'enum', 'Severity', 'low,medium,high');

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_temp',
DisplayName = 'QA Temp',
FieldsAggregate = 'MetadataFields#TEMP';

Taxonomy fields can also be configured through MetadataFields#TEMP by populating the taxonomy-specific columns:

INSERT INTO MetadataFields#TEMP (Key, Type, DisplayName, TaxonomyKey, TaxonomyId, Namespace, MultiSelect, SelectableLevels)
VALUES ('cat', 'taxonomy', 'Category', 'cat-tax', 'tax-12345', 'enterprise', 'false', '1,2,3');

EXECUTE CreateMetadataTemplate
TemplateKey = 'qa_create_taxonomy_temp',
DisplayName = 'QA Taxonomy (#TEMP)',
FieldsAggregate = 'MetadataFields#TEMP';

MetadataFields temporary table schema info:

Column Name	Type	Required	Description
Key	string	true	Unique key of the field within the template.
Type	string	true	Field data type. One of string, float, date, enum, multiSelect, taxonomy.
DisplayName	string	true	Display name shown in the Box web and mobile apps.
Description	string	false	Help text shown alongside the field.
IsHidden	boolean	false	If true, the field is hidden from the Box UI and only accessible through the API.
Options	string	conditional	Required for Type=enum or Type=multiSelect. Comma-separated list of option keys (for example 'low,medium,high').
TaxonomyKey	string	conditional	Required when Type=taxonomy. The taxonomy key.
TaxonomyId	string	conditional	Required when Type=taxonomy. The taxonomy identifier.
Namespace	string	conditional	Required when Type=taxonomy. The taxonomy namespace.
MultiSelect	boolean	conditional	Required when Type=taxonomy. Whether the taxonomy field accepts multiple selections.
SelectableLevels	string	conditional	Required when Type=taxonomy. Comma-separated list of taxonomy levels the user may pick from (for example '1,2,3').

Input

Name	Type	Description
TemplateKey	String	Unique key for the template within the enterprise.
DisplayName	String	Human-readable name of the template, shown in the Box web and mobile apps.
TemplateIsHidden	Boolean	If true, the template is hidden from the Box UI and only available through the API.
CopyInstanceOnItemCopy	Boolean	If true, applied metadata is copied along when the file or folder is duplicated.
FieldsAggregate	String	Optional list of field definitions to seed the new template. Accepts either: a JSON array, or a temporary-table populated by prior INSERTs.
AsUserId	String	Identifier of the user to impersonate. Available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Id	String	Unique identifier assigned by Box to the new template.
Success	Boolean	True when the template was created successfully.

Creates a new electronic signature request for documents stored in Box using Box Sign.

Execute

To create a Sign Request you must provide a JSON object as the payload for the API request in the SignRequestPayload parameter. For the payload structure refer to the Box API Reference here.

EXEC CreateSignRequest SignRequestPayload = '{"are_reminders_enabled":true,"are_text_signatures_enabled":true,"days_valid":10,"declined_redirect_url":"https://cdata.com/","email_message":"Testing CreateSignRequest stored procedure",...}';

Alternatively, you can specify an absolute path to a file containing the JSON object.

EXEC CreateSignRequest SignRequestPayloadPath = 'C:/Users/User/payload.json';

Input

Name	Type	Description
SignRequestPayload	String	A JSON object that defines the details of the Sign Request to be created, including document information, recipients, and settings.
SignRequestPayloadPath	String	The absolute file path to a local JSON file containing the payload for creating a Sign Request.
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
SignRequestAggregate	String	A JSON object representing the newly created Sign Request, including its unique identifier, status, and configuration details.

Delete external users from current user enterprise. This will remove each external user from all invited collaborations within the current enterprise.

Input

Name	Type	Description
ExternalUserId	String	Comma-separated list of external user IDs to delete, e.g. 'userId1,userId2'.

Result Set Columns

Name	Type	Description
ExternalUserId	String	The ID of the external user.
StatusCode	String	HTTP status code for a specific user's deletion request.
StatusMessage	String	Deletion request status details. This property is populated only when the deletion request is not successful.

Permanently deletes a specified file from Box.

Input

Name	Type	Description
FileID	String	Identifier of the file to be deleted. Only files owned by the authenticated user can be deleted.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file deletion operation completed successfully.

Deletes a Box metadata template and all of its fields.

Input

Name	Type	Description
TemplateKey	String	Unique key of the template to delete.
AsUserId	String	Identifier of the user to impersonate. Available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	Boolean	True when the template was deleted successfully.

Downloads the contents of a file from Box to a local system or application.

Input

Name	Type	Description
FileID	String	Identifier of the file to be downloaded.
FileVersionID	String	Identifier of a specific file version to download. If not provided, the most recent version is downloaded.
DownloadTo	String	The file path where the downloaded file should be saved locally.
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.
UseSimpleName	Boolean	When set to true, removes unsupported special characters from the file name to ensure compatibility with operating systems that restrict certain characters. Characters removed include forward slash, backslash, colon, asterisk, question mark, double-quote, less than, greater than, and vertical bar. The default value is false.
Name	String	The name to use when saving the file after download. If left blank, the original file name is used.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file download operation completed successfully.
FileData	String	The downloaded file data encoded in Base64, returned when neither DownloadTo nor FileStream are specified.

Permanently deletes all items currently in the trash for the authenticated user or enterprise.

Input

Name	Type	Description
AsUserId	String	Identifier of the user to impersonate for API requests, available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the trash was successfully emptied.

Locks a file in Box to prevent other users from editing or uploading new versions while the lock is active.

Input

Name	Type	Description
FileId	String	The unique identifier of the file to be locked.
ExpiresAt	Datetime	The timestamp when the lock expires. If not provided, the lock remains until it is manually removed.
PreventDownload	Bool	Indicates whether downloading the locked file should be prevented while the lock is active. The default value is false.
AsUserId	String	The Id of the user to impersonate when performing the operation. Only works with Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	Bool	Indicates whether the file was successfully locked.

Modifies a Box metadata template's schema.

Execute

Modifies the schema of an existing metadata template.

When a required input is missing, the error message names both the input and the current Op value (for example, "Input 'FieldKey' is required when Op='addField' for procedure ModifyMetadataTemplate.") so that callers can tell which value to supply for the operation they requested.

editTemplate

Modify template-level properties (display name, hidden flag, copy-on-copy flag) on an existing template.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'editTemplate',
DisplayName = 'QA Renamed',
TemplateIsHidden = 'true',
CopyInstanceOnItemCopy = 'true';

addField

Add a new field to an existing template. FieldKey, FieldType, and FieldDisplayName are required for every variant.

String field:

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'addField',
FieldKey = 'f3',
FieldType = 'string',
FieldDisplayName = 'Field 3',
FieldDescription = 'Added by addField';

Enum field with options:

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'addField',
FieldKey = 'f4',
FieldType = 'enum',
FieldDisplayName = 'Field 4',
FieldOptions = 'alpha,beta,gamma';

MultiSelect field with options:

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'addField',
FieldKey = 'f5',
FieldType = 'multiSelect',
FieldDisplayName = 'Field 5',
FieldOptions = 'red,green';

editField

Update the display name, description, or hidden flag of an existing field. Pass FieldNewKey to rename the field key in place.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'editField',
FieldKey = 'f1',
FieldDisplayName = 'Field 1 (renamed)',
FieldDescription = 'Renamed by editField',
FieldIsHidden = 'false';

removeField

Remove a single field from the template. Other fields are unaffected.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'removeField',
FieldKey = 'f5';

reorderFields

Reorder the existing fields. Supply the desired order as a comma-separated list of field keys on the FieldKey input.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'reorderFields',
FieldKey = 'f4,f3,f2,f1';

addEnumOption

Append a single option to an existing enum field. FieldKey selects the field; OptionKey is the new option key to add.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'addEnumOption',
FieldKey = 'f2',
OptionKey = 'optC';

editEnumOption

Rename an existing enum option in place. OptionKey identifies the existing option; OptionNewKey is its new key.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'editEnumOption',
FieldKey = 'f2',
OptionKey = 'optA',
OptionNewKey = 'optA_renamed';

removeEnumOption

Remove a single option from an enum field. The field itself is left in place.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'removeEnumOption',
FieldKey = 'f2',
OptionKey = 'optC';

reorderEnumOptions

Reorder the options of an enum field. Supply the desired order as a comma-separated list of option keys on the FieldOptions input.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'reorderEnumOptions',
FieldKey = 'f2',
FieldOptions = 'optC,optB,optA_renamed';

addMultiSelectOption

Append a single option to a multiSelect field. Same input shape as addEnumOption.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'addMultiSelectOption',
FieldKey = 'f5',
OptionKey = 'blue';

editMultiSelectOption

Rename a multiSelect option in place.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'editMultiSelectOption',
FieldKey = 'f5',
OptionKey = 'green',
OptionNewKey = 'green_renamed';

removeMultiSelectOption

Remove a single option from a multiSelect field.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'removeMultiSelectOption',
FieldKey = 'f5',
OptionKey = 'blue';

reorderMultiSelectOptions

Reorder the options of a multiSelect field.

EXECUTE ModifyMetadataTemplate
TemplateKey = 'qa_template',
Op = 'reorderMultiSelectOptions',
FieldKey = 'f5',
FieldOptions = 'blue,green_renamed,red';

Input

Name	Type	Description
TemplateKey	String	Unique key of the template to modify.
Op	String	JSON-patch operation to perform on the template. One of: editTemplate, addField, editField, removeField, reorderFields, addEnumOption, editEnumOption, removeEnumOption, reorderEnumOptions, addMultiSelectOption, editMultiSelectOption, removeMultiSelectOption, reorderMultiSelectOptions.
DisplayName	String	New display name. Used with Op=editTemplate.
TemplateIsHidden	Boolean	Whether the template is hidden from the UI. Used with Op=editTemplate.
CopyInstanceOnItemCopy	Boolean	Whether metadata is copied with the item. Used with Op=editTemplate.
FieldKey	String	Key of the field being added, edited, removed, or whose options are being changed. For Op=reorderFields, supply a comma-separated ordered list.
FieldNewKey	String	The new Key of the field. Use this input when you want to change the field Key. Used with Op=editField.
FieldType	String	Data type of the field (string, float, date, enum, or multiSelect). Used with Op=addField.
FieldDisplayName	String	Display name of the field. Used with addField or editField.
FieldDescription	String	Description of the field. Used with addField or editField.
FieldIsHidden	Boolean	Whether the field is hidden. Used with addField or editField.
FieldOptions	String	List of option keys for enum and multiSelect fields. Used with Op=addField, Op=reorderEnumOptions, or Op=reorderMultiSelectOptions . Accepts a comma-separated list (for example 'a,b,c').
OptionKey	String	Key of the affected enum/multiSelect option. Used with Op that concerns adding, editing, removing an option from an enum or multiSelect field.
OptionNewKey	String	The new Key of the Field Option. Use this input when you want to change the Option Key of the Field. Used with Op=editEnumOption, or =editMultiSelectOption.
AsUserId	String	Identifier of the user to impersonate. Available only for Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	Boolean	True when the template change was applied successfully.

Restores a previously deleted file or folder from the trash back to its original location.

Input

Name	Type	Description
ItemId	String	The Id of the file or folder to be restored from the trash.
ItemType	String	The type of item to restore. Supported values are Files and Folders.
NewName	String	An optional new name to assign to the restored item if you do not want to use the original name.
NewParentId	String	An optional parent folder Id to place the restored item into. Used if the original parent folder no longer exists.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file or folder was successfully restored.
ID	String	The unique identifier of the restored item.
ParentId	String	The identifier of the parent folder containing the restored item.

Revokes the shared link for a file, folder, or bookmark, removing external access.

Input

Name	Type	Description
ItemId	String	The Id of the item whose shared link you want to revoke.
ItemType	String	The type of item. The default value is file.

Result Set Columns

Name	Type	Description
Success	String	Whether or not the shared link was successfully revoked.

Performs a global search across Box to find files, folders, and other items matching a search term.

Input

Name	Type	Description
SearchTerm	String	The keyword or phrase to use when performing a global search across files, folders, and other Box items.

Result Set Columns

Name	Type	Description
Results	String	The raw JSON response containing the results of the global search, including matching items and their details.

Creates or updates a shared link for a file, folder, or bookmark, enabling controlled external access.

Input

Name	Type	Description
ItemId	String	The Id of the item to share.
ItemType	String	The type of item being shared. The allowed values are file, folder, bookmark. The default value is file.
Access	String	Specifies the access level of the shared link. The allowed values are open, company, collaborators. The default value is open.
Unshared_At	Datetime	The expiration date when the shared link is automatically disabled.
Password	String	A password required to access the shared link, if enabled.
Can_Preview	Boolean	Indicates whether the item can be previewed through the shared link. The default value is true.
Can_Download	Boolean	Indicates whether the item can be downloaded through the shared link. Applicable only to files and folders, not bookmarks. Can only be set with open or company access. The default value is true.
AsUserId	String	The Id of the user you want to impersonate. Only works with Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Url	String	The URL of the newly created shared link.
Download_Url	String	The direct download URL for the shared item.
Download_Count	String	The number of times the item has been downloaded from the shared link.
Is_Password_Enabled	String	Indicates whether the shared link is protected by a password.
Unshared_At	String	The expiration date when the shared link was or will be disabled.
Preview_Count	String	The number of times the item has been previewed through the shared link.
Effective_Access	String	The effective access level applied to the shared link, as enforced by administrators.
Success	String	Whether or not the item was successfully shared.

Releases a file lock so that other users can edit or upload a new version.

Input

Name	Type	Description
FileId	String	The Id of the file you want to unlock.
AsUserId	String	The Id of the user you want to impersonate. Only works with Admin, Co-Admin, and Service Accounts.

Result Set Columns

Name	Type	Description
Success	Bool	Indicates whether the file was successfully unlocked.

Updates the content or metadata of an existing file in Box.

Input

Name	Type	Description
FileID	String	The unique identifier of the file to be copied.
NewName	String	An optional new name for the copied file; if not provided, the original name is used.
Description	String	An optional description to assign to the copied file.
Parent	String	The identifier of the parent folder where the copied file should be placed. This can be used to move the file into a different folder.
CanDownload	String	Specifies who can download the copied file. Accepted values are 'open' (available to everyone) or 'company' (restricted to members of the user's enterprise). The allowed values are open, company.
Tags	String	A comma-separated list of tags to associate with the copied file. Each item supports up to 100 tags, with a maximum of 10,000 unique tags allowed per enterprise.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file was successfully copied.
ID	String	The identifier of the newly copied file.

Uploads a new file into a specified Box folder.

Input

Name	Type	Description
FilePath	String	The full local path of the file, including its name, to upload. This takes precedence over the Content input.
ParentId	String	The identifier of the parent folder where the file will be uploaded. If not provided, the file is uploaded to the root folder of the Box account. The default value is 0.
AsUserId	String	The Id of the user to impersonate during the upload. Only works with Admin, Co-Admin, and Service Accounts.
FileName	String	The name of the file to upload, including its extension. Required if Content is specified. If FilePath is also provided, this value overrides the file's original name.
CreatedAt	Datetime	An optional timestamp indicating when the file was originally created. Defaults to the upload time if not set. Format example: '2020-01-01T12:00:00-05:00'.
ModifiedAt	Datetime	An optional timestamp indicating when the file was last modified. Defaults to the upload time if not set. Format example: '2020-01-01T12:00:00-05:00'.
Hash	String	An optional SHA1 hash of the file to validate that the file was not corrupted during upload.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the file was uploaded successfully.
ID	String	The unique identifier of the uploaded file.
VersionID	String	The identifier for the specific version of the uploaded file.
Hash	String	The SHA1 hash of the uploaded file, used for integrity verification.
Etag	String	The entity tag of the uploaded file, used for versioning and concurrency control.

Uploads a new version of an existing file, maintaining version history in Box.

Input

Name	Type	Description
FilePath	String	The full local path of the file, including its name, for the new version upload.
FileId	String	The unique identifier of the file whose version is being updated.
NewName	String	An optional new name for the file. If provided, the file is renamed during the upload of the new version. The name must include the file extension (for example, SampleFile.txt).
AsUserId	String	The Id of the user to impersonate when uploading the new version. Only works with Admin, Co-Admin, and Service Accounts.
ModifiedAt	Datetime	An optional timestamp indicating when the file was last modified. If not set, the upload time is used. Example format: '2020-01-01T12:00:00-05:00'.
Hash	String	An optional SHA1 hash of the file used to verify the file's integrity during upload.
IfMatch	String	An optional concurrency control header. Provide the file's most recent etag to ensure it has not changed before updating. If the etag does not match, the request fails with a 412 Precondition Failed.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the new file version was uploaded successfully.
ID	String	The unique identifier of the updated file.
VersionID	String	The identifier for the specific version of the file that was uploaded.
Hash	String	The SHA1 hash of the uploaded file version, used for integrity verification.
Etag	String	The entity tag of the updated file, used for versioning and concurrency control.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Box:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.
IsInsertable	Boolean	Whether the table can be inserted into.
IsDeleteable	Boolean	Whether rows can be deleted from the table.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Files table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName = 'Files' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.
ColumnType	String	The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.
ColumnCapabilities	Int32	A bit mask denoting the column's write capabilities. The value is the sum of the following: 1 if the column is required for INSERTs, 2 if the column is allowed for INSERTs, and 4 if the column is allowed for UPDATEs. A value of 0 indicates that the write capabilities of the column are unknown or that the column is read-only.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the UploadFile stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'UploadFile' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'UploadFile' AND IncludeResultColumns='True'

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName	String	The name of the data type.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.
Values	String	The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams	Boolean	Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath	Boolean	Whether the parameter is a target path for a schema creation operation.
Default	String	The value used for this parameter when no value is specified.
SpecificName	String	A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsCDataProvided	Boolean	Whether the procedure is added/implemented by CData, as opposed to being a native Box procedure.

Pseudocolumns

Name	Type	Description
IncludeResultColumns	Boolean	Whether the output should include columns from the result set in addition to parameters. Defaults to False.

Describes the primary and foreign keys.

The following query retrieves the primary key for the Files table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Files' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.