Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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. –  Ruud van de Beeten Dec 3 '12 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. –  Ruud van de Beeten Dec 3 '12 at 15:45
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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