Once upon a time, there was to be a SQL Server-based file system for Windows. This file system would not only let you search on file properties like filename and file length, but also on what's known as "Extended Properties". For an idea of what extended properties refers to, have a look at the "Properties" tab on any Office Document, or jpg file (especially if you have a GPS camera) or MP3 files, or…other files like [your favorite file type goes here]. Wikipedia defines extended properties as "a file system feature that enables users to associate computer files with metadata not interpreted by the filesystem". To find some extended properties viewers and searchers, I simply did a search on "Windows OS Extended Properties". The APIs to enumerate these properties are the Windows Shell APIs.
In SQL Server Denali CTP1, you can use a new feature of Fulltext Search to search data stored in FTS, not only on content, but if the IFilter exposes it, on extended properties as well. According to BOL, you first need to create SEARCH_PROPERTY_LISTs and add the extended properties you'd like to search on:
CREATE SEARCH PROPERTY LIST DocumentProperties;
GO
ALTER SEARCH PROPERTY LIST DocumentProperties
ADD 'Title'
WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
PROPERTY_DESCRIPTION = 'System.Title – Title of the item.' );
GO — add more properties
And alter the fulltext index to use your search property list:
ALTER FULLTEXT INDEX ON Production.Document
SET SEARCH PROPERTY LIST DocumentProperties
WITH NO POPULATION;
GO
ALTER FULLTEXT INDEX ON Production.Document
START FULL POPULATION;
the available metadata looks like this:
select * from sys.registered_search_properties
select * from sys.registered_search_property_lists
select * from sys.dm_fts_index_keywords_by_property
Then you'd use them with the improved FTS functions:
SELECT Document FROM Production.Document WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');
Cool, eh? Now Office 2007 and above IFilters support these properties on Office docs. What is needed now is a way to extract property lists using the Windows Shell APIs. And more IFilters that support this. And….given that there's already FTS support over FILESTREAM columns, a way to easily expose the native filesystem as a set of tables with filestream columns.
WinFS, anyone…?