Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_fulltext_database

 

Deprecated procedure. Although the system isn't changed search on "CREATE FULLTEXT CATALOG" in SQL 2005 and higher.
The stored procedure can Enables full-text indexing within the current database.

sp_fulltext_database [@action =] 'action'

Value Description
enable Enables full-text indexing within the current database.

Important  Use carefully. If full-text catalogs already exist, this procedure drops all full-text catalogs, re-creates any full-text indexing indicated in the system tables, and marks the database as full-text enabled.

This action does not cause index population to begin; an explicit start_full or start_incremental on each catalog must be issued using sp_fulltext_catalog to populate or repopulate the full-text index.

disable Removes all full-text catalogs in the file system for the current database and marks the database as being disabled for full-text indexing. This action does not change any full-text index metadata at the full-text catalog or table level.

Disabling full-text indexing does not remove rows from sysfulltextcatalogs and does not indicate that full-text enabled tables are no longer marked for full-text indexing. All the full-text metadata definitions are still in the system tables. It does indicate that full-text indexing is turned off for the database and no full-text indexing activity can occur.

/* We need to enable full text search for Northwind database. We will do that with sp_fulltext_database procedure */
EXEC sp_fulltext_database 'enable'

-- Create fulltext catalog

EXEC sp_fulltext_catalog 'NorthwindCatalog','create'

-- Add some indexes to database

EXEC sp_fulltext_table 'Customers', 'create', 'NorthwindCatalog', 'pk_customers'
EXEC sp_fulltext_table 'Orders', 'create', 'NorthwindCatalog', 'pk_orders'

-- Add columns for searching to full text search index

EXEC sp_fulltext_column 'Customers', 'CompanyName', 'add'
EXEC sp_fulltext_column 'Customers', 'ContactName', 'add'
EXEC sp_fulltext_column 'Customers', 'Address', 'add'
EXEC sp_fulltext_column 'Customers', 'City', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipName', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipAddress', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipCity', 'add'

-- Activate full text search indexes

EXEC sp_fulltext_table 'Customers','activate'
EXEC sp_fulltext_table 'Orders','activate'

-- start full population of catalog

EXEC sp_fulltext_catalog 'NorthwindCatalog', 'start_full'

Now you can perform search on indexed columns using CONTAINS, FREETEXT, CONTAINSTABLE or FREETEXTTABLE keywords. For example, let say I want to check all contacts where first name is Maria or Ana:

USE Northwind
go
SELECT CustomerId, ContactName, CompanyName, Address, City
FROM Customers c INNER JOIN
CONTAINSTABLE(Customers, (ContactName), '"Aria" OR "Ana"') AS KEY_TBL
ON c.CustomerId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
go

 




Syntax
create proc sys.sp_fulltext_database
    @action     varchar(20)     -- 'enable' | 'disable'
as
    declare @ftcat      sysname,
            @ftcatid    smallint,
            @path       nvarchar(260),
            @objid      int,
            @dbid       int,
            @dbname     sysname,
            @objname    sysname,
            @sch_id        int,
            @vc1        nvarchar(517)   -- "[owner].[object]"

    declare @execstring nvarchar (4000)

    set nocount on

    -- sp_fulltext_database will run under read committed isolation level 
    set transaction isolation level READ COMMITTED

    select @dbname = db_name()
    select @dbid = db_id()

    -- CHECK PERMISSIONS (must be a dbowner) 
    if (is_member('db_owner') = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

    if (@dbname in ('master','tempdb','model'))
    begin
        raiserror(9966, -1, -1)
        return 1
    end

    -- VALIDATE PARAMS 
    if @action is null OR @action not in ('enable','disable')
    begin
        raiserror(15600,-1,-1,'sys.sp_fulltext_database')
        return 1
    end

    -- DISALLOW USER TRANSACTION 
    set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sys.sp_fulltext_database')
        return 1
    end

    -- CHECK DATABASE MODE (must not be read-only) 
    if (DATABASEPROPERTY(@dbname, 'IsReadOnly') = 1)
    begin
        raiserror(15635, -1, -1, 'sp_fulltext_database')
        return 1
    end

    -- CHECK IF WE'RE TRANSITIONING FROM ENABLED TO DISABLED STATE 
    if @action = 'disable' and DATABASEPROPERTY(@dbname, 'IsFulltextEnabled') = 1
    begin
        -- CLEAR SYSDATABASES BIT AND PROPAGATE W/ CHECKPOINT 
        EXEC %%DatabaseEx(Name = @dbname).SetFulltextEnabled(Value = 0)
        checkpoint

        -- DROP ALL CATALOGS WITH THIS DATABASE 
        DBCC CALLFULLTEXT ( 7, @dbid )  -- FTDropAllCatalogs ( "@dbid" )
        if @@error <> 0
            return 1
    end

    -- CHECK IF WE'RE TRANSITIONING FROM DISABLED TO ENABLED STATE 
    if @action = 'enable' and DATABASEPROPERTY(@dbname, 'IsFulltextEnabled') = 0
    begin
        -- SET SYSDATABASES BIT AND PROPAGATE W/ CHECKPOINT 
        EXEC %%DatabaseEx(Name = @dbname).SetFulltextEnabled(Value = 1)
        -- CHECKPOINT TO PUSH SYSDATABASES BIT TO MEMORY 
        checkpoint

        -- REBUILD CATALOGS 
        declare ms_crs_ftcat cursor static local for
            select name from sys.fulltext_catalogs
        open ms_crs_ftcat
        fetch ms_crs_ftcat into @ftcat
        while @@fetch_status >= 0
        begin
            select @execstring = 'ALTER FULLTEXT CATALOG '
                + quotename( @ftcat, '[')
                + ' REBUILD '
            EXEC (@execstring)

            fetch ms_crs_ftcat into @ftcat
        end
        deallocate ms_crs_ftcat


    end

	BEGIN TRANSACTION

		-- EMDEventType(x_eet_Alter_Database), EMDUniversalClass(x_eunc_Database), src major id, src minor id, src name
		-- -1 means target ignored, target major id, target minor id, target name,
		-- # of parameters, 5 parameters
		EXEC %%System().FireTrigger(ID = 202, ID = 0, ID = @dbid, ID = 0, Value = @dbname,
			ID = -1, ID = 0, ID = 0, Value = NULL, ID = 1,
			Value = @action, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

	COMMIT TRANSACTION

    -- SUCCESS 
    return 0    -- sp_fulltext_database

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash