April 22, 2012

sp_fulltext_catalog (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_fulltext_catalog(nvarchar @ftcat
, varchar @action
, nvarchar @path)

MetaData:

 create proc sys.sp_fulltext_catalog  
@ftcat sysname, -- full-text catalog name
@action varchar(20), -- create | drop | | rebuild | ...
@path nvarchar(101) = null -- optional file path for create (max of 100 chars!!!)
as
declare @objname sysname,
@tabname nvarchar(517),
@schemaname nvarchar(517)

declare @execstring nvarchar (4000)

set nocount on

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

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

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

-- VALIDATE PARAMS --
if @action is null
OR @action not in ('create','drop','start_full','start_incremental','stop','rebuild')
OR @ftcat is null OR datalength(@ftcat) = 0 -- allow spaces in the name, but not a 0-length string
OR (@path is not null and @action <> 'create')
OR (len(@path) > 100)
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_catalog')
return 1
end

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

declare @ftcatid smallint
select @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @ftcat
if @action not in ('create', 'drop') and @ftcatid is null
begin
declare @curdbname sysname
select @curdbname = db_name()
declare @curdbnamelen int
select @curdbnamelen = LEN(@curdbname)
raiserror(7641,-1,-1,@ftcat, @curdbnamelen, @curdbname)
return 1
end

if @action = 'create'
begin
-- CREATE FULLTEXT CATALOG --
select @execstring = 'CREATE FULLTEXT CATALOG '
+ quotename( @ftcat, '[')
+ CASE
when @path is null then ''
else ' IN PATH '''+REPLACE(@path ,N'''',N'''''')+''''
END

EXEC (@execstring)
end

if @action = 'drop'
begin
-- build DROP FULLTEXT CATALOG --
select @execstring = 'DROP FULLTEXT CATALOG '
+ quotename( @ftcat, '[')

EXEC (@execstring)
end


if @action = 'start_full'
begin
-- FIND all the table and start full crawl --
declare ms_crs_ftind cursor static local for
select T.name, schema_name(T.schema_id)
from sys.fulltext_indexes as FT join sys.objects as T on(FT.object_id = T.object_id)
where FT.fulltext_catalog_id = @ftcatid
open ms_crs_ftind
fetch ms_crs_ftind into @tabname,@schemaname
while @@fetch_status >= 0
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ quotename(@schemaname,'[')+'.'+quotename(@tabname,'[') + ' START FULL POPULATION '
EXEC (@execstring)

fetch ms_crs_ftind into @tabname,@schemaname
end

deallocate ms_crs_ftind
end

if @action = 'start_incremental'
begin
-- FIND all the table and start full crawl --
declare ms_crs_ftind cursor static local for
select T.name, schema_name(T.schema_id)
from sys.fulltext_indexes as FT join sys.objects as T on(FT.object_id = T.object_id)
where FT.fulltext_catalog_id = @ftcatid
open ms_crs_ftind
fetch ms_crs_ftind into @tabname,@schemaname
while @@fetch_status >= 0
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ quotename(@schemaname,'[')+'.'+quotename(@tabname,'[') + ' START INCREMENTAL POPULATION '
EXEC (@execstring)

fetch ms_crs_ftind into @tabname,@schemaname
end

deallocate ms_crs_ftind
end

if @action = 'stop'
begin
-- FIND all the table and start full crawl --
declare ms_crs_ftind cursor static local for
select T.name, schema_name(T.schema_id)
from sys.fulltext_indexes as FT join sys.objects as T on(FT.object_id = T.object_id)
where FT.fulltext_catalog_id = @ftcatid
open ms_crs_ftind
fetch ms_crs_ftind into @tabname,@schemaname
while @@fetch_status >= 0
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ quotename(@schemaname,'[')+'.'+quotename(@tabname,'[') + ' STOP POPULATION '
EXEC (@execstring)

fetch ms_crs_ftind into @tabname,@schemaname
end

deallocate ms_crs_ftind
end

if @action = 'rebuild'
begin
-- REBUILD FULLTEXT CATALOG --
select @execstring = 'ALTER FULLTEXT CATALOG '
+ quotename( @ftcat, '[') +' REBUILD '

EXEC (@execstring)

end

return 0 -- sp_fulltext_catalog

No comments:

Post a Comment

Total Pageviews