CREATE SEARCH PROPERTY LIST (Transact-SQL)

new_list_name

Is the name of the new search property list. new_list_name is an identifier with a maximum of 128 characters. new_list_name must be unique among all property lists in the current database, and conform to the rules for identifiers. new_list_name will be used when the full-text index is created.

database_name

Is the name of the database where the property list specified by source_list_name is located. If not specified, database_name defaults to the current database.

database_name must specify the name of an existing database. The login for the current connection must be associated with an existing user ID in the database specified by database_name. You must also have the required permissions on the database.

source_list_name

Specifies that the new property list is created by copying an existing property list from database_name. If source_list_name does not exist, CREATE SEARCH PROPERTY LIST fails with an error. The search properties in source_list_name are inherited by new_list_name.

AUTHORIZATION owner_name

Specifies the name of a user or role to own of the property list. owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. If not specified, ownership is given to the current user.

Note
The owner can be changed by using the ALTER AUTHORIZATION Transact-SQL statement.

Note
For information about property lists in general, see Search Document Properties with Search Property Lists.

By default, a new search property list is empty and you must alter it to manually to add one or more search properties. Alternatively, you can copy an existing search property list. In this case, the new list inherits the search properties of its source, but you can alter the new list to add or remove search properties. Any properties in the search property list at the time of the next full population are included in the full-text index.

A CREATE SEARCH PROPERTY LIST statement fails under any of the following conditions:

• If the database specified by database_name does not exist.

• If the list specified by source_list_name does not exist.

• If you do not have the correct permissions.

To add or remove properties from a list

• ALTER SEARCH PROPERTY LIST (Transact-SQL)

• To drop a property list

• DROP SEARCH PROPERTY LIST (Transact-SQL)

Requires CREATE FULLTEXT CATALOG permissions in the current database and REFERENCES permissions on any database from which you copy a source property list.

Note
REFERENCES permission is required to associate the list with a full-text index. CONTROL permission is required to add and remove properties or drop the list. The property list owner can grant REFERENCES or CONTROL permissions on the list. Users with CONTROL permission can also grant REFERENCES permission to other users.

A. Creating an empty property list and associating it with an index

The following example creates a new search property list named DocumentPropertyList. The example then uses an ALTER FULLTEXT INDEX statement to associate the new property list with the full-text index of the Production.Document table in the AdventureWorks database, without starting a population.

Note
For an example that adds several predefined, well-known search properties to this search property list, see ALTER SEARCH PROPERTY LIST (Transact-SQL). After adding search properties to the list, the database administrator would need to use another ALTER FULLTEXT INDEX statement with the START FULL POPULATION clause.

Copy

CREATE SEARCH PROPERTY LIST DocumentPropertyList;
GO
USE AdventureWorks;
ALTER FULLTEXT INDEX ON Production.Document 
   SET SEARCH PROPERTY LIST DocumentPropertyList
   WITH NO POPULATION; 
GO 

B. Creating a property list from an existing one

CREATE SEARCH PROPERTY LIST JobCandidateProperties FROM AdventureWorks.DocumentPropertyList;
GO
ALTER FULLTEXT INDEX ON HumanResources.JobCandidate 
   SET SEARCH PROPERTY LIST JobCandidateProperties;
GO

Reference

Concepts