Querying a SharePoint list with SQL: four routes compared

23 October 2026

Tags: Relational databasesDesktopCommand line

“I just want to run SELECT * FROM MaintenanceRequests WHERE Status = 'Open' and join it to the invoices table.” That is how a finance analyst at a property management company put it. Her team logs every repair job in a SharePoint list called Maintenance Requests, about 18,000 items now, with a Property lookup, an Assigned To person column and a multi-choice Trades column. Contractor invoices live in a SQL Server finance database. Every month she exports the list to Excel, pastes it into a staging table and matches invoices to jobs by hand.

Search for a way to query the list with SQL and you find forum threads from 2007, vendor driver pages and a few answers telling you to read the SharePoint database directly. Each route does something real. Here is what each one does, where it breaks, and which job each one fits.

Can you query a SharePoint list with SQL?

Not directly. A SharePoint list has no SQL endpoint. In SharePoint Online the supported ways to read list data are the SharePoint REST service and Microsoft Graph, both web APIs. Microsoft’s REST service overview describes it as a way to “interact remotely with SharePoint data by using any technology that supports REST web requests”.

So every way of putting SQL in front of a list does one of two things. It translates SQL into API calls at query time, which is what ODBC and JDBC drivers do, and what a linked server does through such a driver. Or it copies the list into a database first and runs the SQL there. The fourth idea people find, querying SharePoint’s own SQL Server database, is covered in its own section below, because the answer differs between SharePoint Online and SharePoint Server.

How a SharePoint list ODBC or JDBC driver works

A SharePoint driver presents each list as a table. You install it, give it a site address and a way to sign in, and tools that speak ODBC (Excel, Power BI Desktop, Tableau, SQL Server) or JDBC (Java applications, DBeaver, many reporting tools) see MaintenanceRequests as something they can SELECT from. Behind that table the driver sends REST or Graph requests, pages through the results and hands back rows.

The vendors that rank for “sharepoint list odbc driver” are established ones. Progress DataDirect sells ODBC and JDBC drivers for Microsoft SharePoint whose product pages list SharePoint Online lists as the supported source. CData sells a SharePoint driver as part of a catalogue covering hundreds of sources, and its Sync product replicates through the same driver layer; our SPSync and CData Sync comparison covers that product. ZappySys sells an ODBC driver that reads SharePoint Online through Microsoft Graph with an app registration you create.

For the right job these drivers are good. The data is live, there is nothing to schedule, and an analyst can pull a few hundred rows from a small list into Excel or Tableau with the tools she already has. Some drivers write back as well, which a copy cannot do.

Where a driver starts to hurt: large lists and throttling

Every query is API traffic, and SharePoint Online meters API traffic. Microsoft’s page on avoiding throttling prices a Microsoft Graph multi-item request at 2 resource units and a single-item request at 1 resource unit. The same page says REST calls have no fixed cost and usually consume more resource units than Graph does for the same work. It gives each app in a tenant of 1,001 to 5,000 licences 2,500 resource units a minute. Our throttling article works through those limits in detail.

Take the Maintenance Requests list. If a driver pages at 200 items, a full read of 18,000 items is 90 requests, or 180 resource units through Graph, and more through REST. Once, that is nothing. Put the query behind a morning report that 40 people open and it becomes at least 7,200 resource units before ten o’clock, all charged to the driver’s app registration. Each of those 40 people waits for 90 round trips to SharePoint as well.

Filters are the second issue. In SharePoint Online, a list over 5,000 items hits the list view threshold for queries that cannot use an index. A driver that passes WHERE Status = 'Open' down to SharePoint can get an error back on an unindexed column. A driver that avoids the error by reading all 18,000 rows and filtering locally pays for a full read every time.

Joins are the third. SharePoint’s APIs can expand a lookup to fetch a few fields of the related item, but they cannot run an arbitrary join. A join between Maintenance Requests and Properties therefore usually happens in the driver or the client, after both lists have been read. Lookup and person columns also arrive in whatever shape the driver chooses: often an id, sometimes a display name, and multi-choice values usually as one delimited string.

How do you set up a SharePoint list as a linked server in SQL Server?

A linked server lets SQL Server query an outside source with four-part names or OPENQUERY. There is no SharePoint linked server provider, so the route is the ODBC driver from the previous section wrapped in Microsoft’s OLE DB provider for ODBC, MSDASQL. Install the driver on the SQL Server machine, create a System DSN for the site, then:

EXEC sp_addlinkedserver
    @server     = N'SPLISTS',
    @srvproduct = N'',
    @provider   = N'MSDASQL',
    @datasrc    = N'SharePointEstates';   -- the System DSN

SELECT *
FROM OPENQUERY(SPLISTS,
    'SELECT ID, Title, Status FROM MaintenanceRequests WHERE Status = ''Open''');

The sp_addlinkedserver reference shows this MSDASQL and @datasrc pattern and notes that a linked server using MSDASQL with a provider string can fail with error 7416 on SQL Server 2022 and 2025, which is one reason to use a System DSN. OPENQUERY sends the inner statement to the driver as written, so the filter goes where the driver puts it rather than where SQL Server’s optimiser decides.

Two limits decide whether this is open to you. The linked servers overview says linked servers are not available in Azure SQL Database, and the sp_addlinkedserver page says Azure SQL Managed Instance accepts only SQL Server, SQL Database and other managed instances as remote sources. A driver-backed linked server to SharePoint therefore needs SQL Server on a machine you control.

Everything from the driver section still applies, with one addition. When you join a local table to a four-part name, SQL Server can choose a plan that runs the remote side once per local row. Matching 2,000 invoices to their jobs that way can mean 2,000 separate requests to SharePoint, each charged at least 1 resource unit and each a round trip. Wrapping the remote side in OPENQUERY and staging it into a temporary table first avoids that, at which point you have built a small copy by hand.

Can you query the SharePoint content database for list items?

In SharePoint Online, no. The content databases sit inside Microsoft’s service. Microsoft’s SharePoint and OneDrive data resiliency page describes SharePoint storing its metadata in Azure SQL Database, run and failed over by Microsoft’s own automation. Tenants get no connection string or login for those databases; the documented ways in are the REST service and Graph. Any answer telling you to query AllUserData or a content database is written for SharePoint Server.

On SharePoint Server it is possible, and Microsoft warns against it. Microsoft’s article Unsupported and supported SharePoint database changes says that “programmatically or manually reading from SharePoint Server databases might cause unexpected locking in SQL Server”. Reads that use queries or scripts not provided by Microsoft count as unsupported once Microsoft Support identifies them as a barrier to resolving a case. The database is then in an unsupported state until those reads stop. Changes are stricter still: a third-party tool that changes data in the databases makes the whole farm unsupported.

There is a practical problem as well. List items do not sit in a table per list. Columns from every list share generic storage, so a working query has to reproduce how SharePoint maps your columns, and that mapping belongs to SharePoint’s internal storage layout.

If you are on SharePoint Server and want SQL over list data, read it through the server’s APIs, or move the lists to SharePoint Online and replicate them from there. SPSync reads SharePoint Online and Microsoft Lists only; the on-premises exit page covers that order of work.

A SharePoint list as a SQL table: replicate it

The route that gives you real tables is a copy. SPSync reads each list once in full, then reads only the changes with Microsoft Graph delta queries, which Microsoft prices at 1 resource unit a request once a delta token is in hand. It writes the rows into SQL Server, PostgreSQL or another database you run, every few minutes, and runs a full reconcile every seven days by default as a check against the source. Your queries then run against the database and cost SharePoint nothing.

The copy is also shaped for SQL. A lookup arrives as a key column you can join on with its display value beside it, each multi-value column becomes a child table with one row per value, and every person resolves through one shared sp_users table. Deleted items stay in the table with sp_deleted_at set. The schema reference has the full naming rules and type mapping.

With the estates site replicated using the table prefix estates, the analyst’s database holds these tables (trimmed to the columns used below):

estates_maintenance_requests          sp_item_id, title, status, priority,
                                      property_id, property_value,
                                      assigned_to_user_id, reported_on,
                                      cost_estimate, sp_modified, sp_deleted_at
estates_maintenance_requests__trades  sp_item_id, ordinal, value
estates_properties                    sp_item_id, title, region, cost_centre
sp_users                              id, display_name, email

Sample queries against the replica

The query she wanted in the first place, with property and assignee by name:

SELECT r.sp_item_id, r.title, r.property_value AS property,
       u.display_name AS assigned_to, r.reported_on
FROM estates_maintenance_requests AS r
LEFT JOIN sp_users AS u ON u.id = r.assigned_to_user_id
WHERE r.status = 'Open'
  AND r.sp_deleted_at IS NULL
ORDER BY r.reported_on;

Open jobs per trade, which a delimited string makes painful and a child table makes ordinary:

SELECT t.value AS trade, COUNT(*) AS open_jobs
FROM estates_maintenance_requests AS r
JOIN estates_maintenance_requests__trades AS t ON t.sp_item_id = r.sp_item_id
WHERE r.status = 'Open'
  AND r.sp_deleted_at IS NULL
GROUP BY t.value
ORDER BY open_jobs DESC;

The join to finance. With the replica in a database on the same SQL Server instance as the finance database, a three-part name is enough, and no linked server is involved:

SELECT p.region, p.cost_centre,
       SUM(i.net_amount)    AS invoiced,
       SUM(r.cost_estimate) AS estimated
FROM Finance.dbo.PurchaseInvoices AS i
JOIN estates_maintenance_requests AS r ON r.sp_item_id = i.maintenance_request_id
JOIN estates_properties AS p ON p.sp_item_id = r.property_id
WHERE i.invoice_date >= '2026-10-01'
GROUP BY p.region, p.cost_centre;

That query reads two local databases and makes no request to SharePoint, however many people run it. If the replica lives on a different SQL Server, a linked server between two SQL Servers is a supported, ordinary setup, including on Managed Instance. The SQL Server destination page walks through the setup and the reporting views, and the Postgres starter kit has the same kind of worked schema and queries for PostgreSQL.

What you give up is liveness. The copy is as current as the last sync, a few minutes behind SharePoint, and it is one way: edits are made in SharePoint, which stays the system of record.

Choosing a route for each job

Use a driver when the list is small, the query is occasional and live data matters more than speed: an analyst checking a few hundred rows in Excel, or a Java tool that needs to read one list now and then. Commercial drivers do that job well, and some can write back.

A driver-backed linked server is reasonable only when you run SQL Server yourself, the query is light, and you can wrap it in OPENQUERY so you control what reaches SharePoint. Do not build a nightly job or a busy report on one.

Do not query the content database. In SharePoint Online it is not reachable, and in SharePoint Server Microsoft treats it as unsupported.

Use a replica when the list is large, the query joins to other data, many people or processes run it, or you want history such as deleted items. Most reporting, application and invoice-matching work falls here, and so does anything an AI agent or a scheduled job will query many times a day.