SQL Server Filetable document properties

Question

I'm using SQL Server 2012's Filetable to store documents and search them with Semantic Search.

I was wondering if there is a way to list all the document properties (meta data). There is a way to let Full-Text Search index and search through document properties. I can make a list of properties SQL Server Indexes with the following statement:

 SELECT * FROM sys.registered_search_properties; 

I can also extend this list with SQL or using a program.

What I didn't find is a way to list the actual information. What I'm looking for is a list like:

• Author: Ruud van de Beeten
• Title: Test document
• custom property: custom value

Can anyone point me in the right direction?

Edit: Bob Beauchemin created a ticket describing my problem. As the DMV is not listing the property values, I can't use it in my project.

I ended up using C# to list custom properties with the OleDocumentProperties object. This object can read properties from an Office document. I'll keep watching this question, hoping for a better solution.

Answer 1

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough.

SELECT
    keyword,
    display_term,
    column_id, document_id, property_id
FROM sys.dm_fts_index_keywords_by_property
(
    DB_ID('FileTableDB'), 
    OBJECT_ID('FileTableTb')
);

MSDN on that DMV - http://msdn.microsoft.com/en-us/library/ee677646.aspx

Here is a discussion with Bob Beauchemin and I on the topic: https://twitter.com/bobbeauch/status/275101491084292096

Bob told me about the DMV mentioned above and pretty much confirmed that it is the most likely way.

Here is the Connect ticket Bob created for this topic: https://connect.microsoft.com/SQLServer/feedback/details/773212/provide-complete-fts-property-values-in-a-dmv-side-table

We also go on to talk about how it might be better to have a preprocessor of sorts to pull the meta info out, store it, (not sure if you normalize format of your docs or not), and then reprocess if modified. Bob suggests perhaps being able to pull this off with a SQLCLR trigger/proc which would be a rather clever solution and get your hands all kinds of dirty.

Answer 2

Found beta of a product that does this because I was asked the question directly. Not affiliated with this company is any way. See "How To Return Document Property Values in Full-Text Search" at: http://www.sqlskills.com/blogs/bobb/.