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.


2 Answers 2


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.

    column_id, document_id, property_id
FROM sys.dm_fts_index_keywords_by_property

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.

  • thank you for taking the time to answer my question. I found out about the dmv, but I can't use it. The ticket Bob created describes best what my problem is. Dec 3, 2012 at 15:42
  • This is the best (and only) answer so far. I ended up using C# to list custom properties with the OleDocumentProperties object. I'll watch this question, hoping for a solution. Dec 3, 2012 at 15:45

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/.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.