Feb
22
2012

Querying for registered search properties and lists in SQL Server 2012

This post discusses the new Property Search feature in SQL Server 2012 "Denali" Full-Text Search. In particular, it shows you how to get a list of the search properties that you've registered and the search property lists to which they belong by using a JOIN.

SQL Server 2012 "Denali" now lets you search specifically for the values of built-in or custom document properties, such as the "Author" of a Microsoft Word document. However, before you can run queries, you have to register the properties that you want to be able to search.

For more information about property search in SQL Server 2012 Full-Text Search, see Search Document Properties with Search Property Lists.

Let's walk through the steps of registering some search property lists and search properties. Then we'll show how to query this information.

Thank you to Venkatraman Parameswaran, friend and SQL Server tester, for the code samples in this blog post.

1. Choose a database. First, select the database that you want to use for your testing. The following example creates a new test database just to be safe:

use master 
go
if exists(select * from sys.databases where [name] = 'testDB1') drop database testDB1
go
create database testDB1
go
use testDB1
go

 

2. Create search property lists. Now let's create a few search property lists for our testing. Later we'll add search properties to these lists. Search property lists are simply convenient containers for organizing groups of search properties.

--  Create a few search property lists.
create search property list searchpropertylist_1;
create search property list searchpropertylist_2;
create search property list searchpropertylist_3;
go

 

3. Add search properties. Now let's add some search properties to the search property lists that we've created. For more information about how to find the values that you need to write these statements, see Find Property Set GUIDs and Property Integer IDs for Search Properties. Watch for a future blog post about this subject.

-- Add a few properties to each list. Add some to more than one list.
ALTER SEARCH PROPERTY LIST searchpropertylist_1 ADD N'System.Author'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = N'System.Author');
ALTER SEARCH PROPERTY LIST searchpropertylist_1 ADD N'System.Subject'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 3, PROPERTY_DESCRIPTION = N'System.Subject');
ALTER SEARCH PROPERTY LIST searchpropertylist_1 ADD N'System.Title'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Title');

ALTER SEARCH PROPERTY LIST searchpropertylist_2 ADD N'System.Category'
WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Category');
ALTER SEARCH PROPERTY LIST searchpropertylist_2 ADD N'System.Comment'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 6, PROPERTY_DESCRIPTION = N'System.Comment');
ALTER SEARCH PROPERTY LIST searchpropertylist_2 ADD N'System.Company'
WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
PROPERTY_INT_ID = 15, PROPERTY_DESCRIPTION = N'System.Company');
ALTER SEARCH PROPERTY LIST searchpropertylist_2 ADD N'System.ContentStatus'
WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
PROPERTY_INT_ID = 27, PROPERTY_DESCRIPTION = N'System.ContentStatus');

ALTER SEARCH PROPERTY LIST searchpropertylist_3 ADD N'System.Author'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = N'System.Author');
ALTER SEARCH PROPERTY LIST searchpropertylist_3 ADD N'System.Category'
WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Category');
ALTER SEARCH PROPERTY LIST searchpropertylist_3 ADD N'System.Keywords'
WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 5, PROPERTY_DESCRIPTION = N'System.Keywords');

GO

 

4. Query for registered search property lists. Now we're ready to run some queries about the items that we've registered. First, let's query for the search property lists that we've created.

-- Query for all registered search property lists.
select * from sys.registered_search_property_lists
go

Results:

property_list_id name create_date modify_date principal_id
---------------- ---------------------- ----------------------- ----------------------- ------------
5 searchpropertylist_1 2012-02-21 10:35:04.463 2012-02-21 10:35:04.510 1
6 searchpropertylist_2 2012-02-21 10:35:04.477 2012-02-21 10:35:04.560 1
7 searchpropertylist_3 2012-02-21 10:35:04.480 2012-02-21 10:35:04.560 1

(3 row(s) affected)

 

5. Query for registered search properties. Now let's query for all the search properties that we've registered across all search property lists.

-- Query for all registered search properties across all lists.
select * from sys.registered_search_properties
go

Results:

property_list_id property_id property_name property_set_guid property_int_id property_description
---------------- ----------- ---------------------- ------------------------------------ --------------- --------------------
5 1 System.Author F29F85E0-4FF9-1068-AB91-08002B27B3D9 4 System.Author
5 2 System.Subject F29F85E0-4FF9-1068-AB91-08002B27B3D9 3 System.Subject
5 3 System.Title F29F85E0-4FF9-1068-AB91-08002B27B3D9 2 System.Title
6 1 System.Category D5CDD502-2E9C-101B-9397-08002B2CF9AE 2 System.Category
6 2 System.Comment F29F85E0-4FF9-1068-AB91-08002B27B3D9 6 System.Comment
6 3 System.Company D5CDD502-2E9C-101B-9397-08002B2CF9AE 15 System.Company
6 4 System.ContentStatus D5CDD502-2E9C-101B-9397-08002B2CF9AE 27 System.ContentStatus
7 1 System.Author F29F85E0-4FF9-1068-AB91-08002B27B3D9 4 System.Author
7 2 System.Category D5CDD502-2E9C-101B-9397-08002B2CF9AE 2 System.Category
7 3 System.Keywords F29F85E0-4FF9-1068-AB91-08002B27B3D9 5 System.Keywords

(10 row(s) affected)

 

6. Query for a combined list of search properties and search property lists. Finally we're ready to use a JOIN to get a combined list of search properties and the search property lists to which they belong.

--  Query to associate properties with the property list.
select A.property_name AS 'Search Property',
B.[name] AS 'Search Property List',
A.property_set_guid,
A.property_int_id
from sys.registered_search_properties A
join sys.registered_search_property_lists B
on A.property_list_id = B.property_list_id
order by B.name
go

Results:

Search Property Search Property List property_set_guid property_int_id
---------------------- --------------------- ------------------------------------ ---------------
System.Author searchpropertylist_1 F29F85E0-4FF9-1068-AB91-08002B27B3D9 4
System.Subject searchpropertylist_1 F29F85E0-4FF9-1068-AB91-08002B27B3D9 3
System.Title searchpropertylist_1 F29F85E0-4FF9-1068-AB91-08002B27B3D9 2
System.Category searchpropertylist_2 D5CDD502-2E9C-101B-9397-08002B2CF9AE 2
System.Comment searchpropertylist_2 F29F85E0-4FF9-1068-AB91-08002B27B3D9 6
System.Company searchpropertylist_2 D5CDD502-2E9C-101B-9397-08002B2CF9AE 15
System.ContentStatus searchpropertylist_2 D5CDD502-2E9C-101B-9397-08002B2CF9AE 27
System.Author searchpropertylist_3 F29F85E0-4FF9-1068-AB91-08002B27B3D9 4
System.Category searchpropertylist_3 D5CDD502-2E9C-101B-9397-08002B2CF9AE 2
System.Keywords searchpropertylist_3 F29F85E0-4FF9-1068-AB91-08002B27B3D9 5

(10 row(s) affected)

 

I hope you find this new feature in SQL Server 2012 useful! Look for more blog posts about property search.

-Doug

Pingbacks and trackbacks (1)+