SharePoint document library metadata in SQL Server, with folders and content types

23 October 2026

Tags: Relational databasesDesktopCommand line

The legal team keeps every supplier contract in one SharePoint document library. There are folders by supplier, a Contract content type and an NDA content type, and a document set for each tender. Every contract should have an Expiry date, a Contract value and a Contract owner. The head of legal wants a report: how many contracts each supplier folder holds, which ones are missing an expiry date, and whether the same signed PDF has been uploaded to three different folders.

The library view cannot answer that. Export to Excel gives one view’s rows at a time, folders and all, and someone has to tidy it up before every meeting. A PnP PowerShell script written last year produced a CSV once and was never run again. What the team actually wants is the library’s metadata as a table they can query in SQL Server, kept current without anyone re-running anything.

Why a library report is harder than it looks

A document library looks like a list with files attached, and in SharePoint that is what it is. The trouble starts when you try to report across it. Folders hide documents from a flat view unless the view is set to show items without folders. Each content type brings its own columns, so a contract and an NDA carry different fields. People columns come back as ids that need resolving. And once a library passes the 5,000-item list view threshold, filtering through the API depends on indexed columns. Microsoft Learn’s list items page says the service can filter on only one indexed field at a time.

A script can work through all of this, page by page. PnP PowerShell’s Get-PnPListItem with a page size reads every item in a library, folders included, and each item’s FileDirRef field gives you the folder it sits in. For a one-off audit that is a good answer, and it is how many library reports start. The cost is that the script becomes something to maintain, and a CSV produced on Monday is wrong by Wednesday.

A Power Automate flow is the other common route. The SharePoint connector’s Get files (properties only) action returns a library’s items with their columns, and a SQL Server action can write each one to a table. Then the flow has to decide, item by item, whether a row is new or changed. Get files returns what is in the library now, so a deleted document simply stops appearing, and unless the flow compares the two sides the table keeps contracts that left the library months ago. Adding a column to the library means editing the flow and the table by hand. Both routes work for a small library that rarely changes. Replication is for the library that the business keeps filing into every day.

How do you get document library metadata into SQL Server?

SPSync replicates a document library the same way it replicates a list. You pick the library in the desktop app or in a command-line config, choose SQL Server as the destination, and run. The first run reads every item. After that, each run reads only the items that changed, every five minutes by default in the desktop app. The result is one table per library, named from your table prefix and the library’s name, so the Contracts library with a prefix of legal becomes legal_contracts.

SPSync reads metadata only. It never downloads the files, so the size of the PDFs makes no difference to what each run reads.

One row per item: files, folders and document sets

Every item in the library gets a row, and that includes folders and document sets as well as files. The sp_content_type column tells them apart. A file carries its own content type name, such as Contract or NDA. Folders carry Folder. A document set carries the name of its document set content type, Tender pack in our example.

Person columns hold a user id and join to the shared sp_users table for a display name and login. A lookup column gives you the looked-up item’s id to join on and its display value beside it, and a multi-choice column becomes a child table with one row per value.

A folder or document set row carries its own metadata columns, so a tender pack’s own fields sit on its row. The folder’s name is not one of the columns, and the path columns are covered in the folder path section below.

Content types share one table

A library with several content types still replicates to one table. Its columns are the union of every column the library carries, so an NDA row has an empty contract_value if NDAs have no such column. That is easy to work with once you know it: build one view per content type, with only the columns that apply.

CREATE VIEW legal.contracts_only AS
SELECT sp_item_id, title, expiry_date, contract_value,
       contract_owner_user_id, sp_modified
FROM legal.legal_contracts
WHERE sp_deleted_at IS NULL
  AND sp_content_type = 'Contract';

Two things to know before you write views. Managed metadata columns are skipped today, so a Region term column will not appear yet. And on a real library, SharePoint reports a few bookkeeping fields, such as the check-in comment and comment and like counts, as ordinary columns, so they replicate too. Leave them out of your views. We are working on excluding them by default.

Where does the folder path come from?

Not reliably from the replica today, and this is the part to plan around. The table has five file columns: file_name, file_url, file_size, file_hash and file_folder_path. They are meant to come from the file side of each library item, which Microsoft Graph exposes through the driveItem relationship. Since the library read changed to request each item’s driveItem, these columns have not been confirmed on a live tenant. Our schema reference still documents them as null there, and our tests fill them only against our own Graph simulator, so we do not claim them. The content type, the custom columns and the dates are filled as on any list.

Until then, a short script can fill a side table of your own. For each file row, ask Graph for /sites/{sp_site_id}/lists/{sp_list_id}/items/{sp_item_id}/driveItem. The response carries the file’s name, its webUrl, its size, parentReference.path for the folder and file.hashes.quickXorHash. Store them keyed on sp_item_id:

CREATE TABLE legal.contract_files (
  sp_item_id   BIGINT PRIMARY KEY,
  file_name    NVARCHAR(400),
  file_url     NVARCHAR(2000),
  file_size    BIGINT,
  folder_path  NVARCHAR(2000),
  quick_xor    NVARCHAR(64)
);

Microsoft documents the parent path as percent-encoded and prefixed with the drive, so strip everything up to root: and decode it before you store it. Run the script for rows whose sp_modified is newer than its last run, and it stays small. When live runs confirm the replica’s own file columns, drop the side table and switch the joins over.

Queries a library report needs

With the replica and the side table in place, the head of legal’s questions turn into short queries. Contracts missing an expiry date, with the owner’s name, need only the replica:

SELECT c.sp_item_id, c.title, u.display_name AS owner, c.sp_modified
FROM legal.legal_contracts AS c
LEFT JOIN legal.sp_users AS u ON u.id = c.contract_owner_user_id
WHERE c.sp_deleted_at IS NULL
  AND c.sp_content_type = 'Contract'
  AND c.expiry_date IS NULL
ORDER BY c.sp_modified DESC;

Contracts per supplier folder join the side table on the item id:

SELECT f.folder_path, COUNT(*) AS contracts
FROM legal.legal_contracts AS c
JOIN legal.contract_files AS f ON f.sp_item_id = c.sp_item_id
WHERE c.sp_deleted_at IS NULL
  AND c.sp_content_type = 'Contract'
GROUP BY f.folder_path
ORDER BY contracts DESC;

Documents inside a document set sit in a folder named after the set, so the documents in each tender pack group on their folder path. Filter with LIKE '/Tenders/%' to see every tender at once. The same queries run on PostgreSQL, where the replica has the same columns and the string functions differ slightly.

Can file hashes find duplicate documents?

They can point you at likely duplicates. Graph’s quickXorHash is described in Microsoft’s hashes reference as a hash that can tell whether a file’s contents changed, available where SharePoint provides it. Two files with the same hash and the same size are very likely the same file uploaded twice:

SELECT quick_xor, file_size, COUNT(*) AS copies,
       STRING_AGG(folder_path + '/' + file_name, '; ') AS locations
FROM legal.contract_files
WHERE quick_xor IS NOT NULL
GROUP BY quick_xor, file_size
HAVING COUNT(*) > 1
ORDER BY copies DESC;

STRING_AGG needs SQL Server 2017 or later. Treat the result as a list to review, and open the files before anyone deletes a copy. The same name in two folders is not a conflict for the replica, since each item keeps its own sp_item_id.

What the replica leaves out

SPSync does not copy file contents, version history or item permissions. The copy runs one way, and SharePoint stays the system of record, so people keep filing documents and editing columns in SharePoint and the table follows. Renames and moves arrive as ordinary changes on the next run, and a deleted document keeps its row with sp_deleted_at set, so the report can show what was removed and when. A reconcile compares every item id with SharePoint, weekly by default and configurable, as a QA check and after an outage.

If you need the text inside the files, the catalogue tells your own extraction step which files to fetch. Library metadata is the catalogue an AI assistant needs walks through that for an assistant, and the document metadata for AI use case sets out the pattern.

The SQL Server and PostgreSQL pages describe each destination, and licensing explains how the families are sold. The same library can go to more than one destination, one replication each, and if the database you need is not on the destinations list, tell us. Book a call and we will run it against one of your own libraries.