April 22, 2012

sp_fulltext_table (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_table(nvarchar @tabname
, varchar @action
, nvarchar @ftcat
, nvarchar @keyname)

MetaData:

 create proc sys.sp_fulltext_table  
@tabname nvarchar(517),
@action varchar(50),
@ftcat sysname = NULL, -- create: catalog name
@keyname sysname = NULL -- create: name of unique index
as

declare @execstring nvarchar (4000)
declare @newtabname nvarchar (1035)

set nocount on

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

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

-- add quote to table name. fn_quotefourpartname can add quote to four part name --
select @newtabname=sys.fn_quotefourpartname(@tabname,N'[')

if @newtabname is null
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_table')
return 1
end

-- VALIDATE TABLE NAME --
-- (1) Must exist in current database
declare @objid int
select @objid = object_id(@newtabname, 'local')
if @objid is null
begin
declare @curdbname sysname
select @curdbname = db_name()
raiserror(15009,-1,-1 ,@tabname, @curdbname)
return 1
end

-- CHECK PERMISSION ON TABLE --
if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
begin
raiserror(15247,-1,-1)
return 1
end

-- VALIDATE PARAMS --
if @action is null
OR @action not in ('create','drop','activate','deactivate',
'start_change_tracking', 'stop_change_tracking',
'start_background_updateindex', 'stop_background_updateindex',
'update_index', 'start_full', 'start_incremental', 'stop')
OR (@action not in ('create') and (@ftcat is not null or @keyname is not null))
OR (@action in ('create') and (@ftcat is null or len(@ftcat) = 0 or @keyname is null or len(@keyname) = 0))
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_table')
return 1
end

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


if @action = 'create'
begin
select @execstring = 'CREATE FULLTEXT INDEX ON '
+ @newtabname +' KEY INDEX '
+ quotename( @keyname, '[') + ' ON '
+ quotename( @ftcat, '[') + ' WITH CHANGE_TRACKING OFF, NO POPULATION '
EXEC (@execstring)
end

if @action = 'drop'
begin
select @execstring = 'DROP FULLTEXT INDEX ON '
+ @newtabname

EXEC (@execstring)
end

if @action = 'activate'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' ENABLE '

EXEC (@execstring)
end

if @action = 'deactivate'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' DISABLE '

EXEC (@execstring)
end

if @action = 'start_change_tracking'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING MANUAL '

EXEC (@execstring)
end

if @action = 'stop_change_tracking'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING OFF '

EXEC (@execstring)
end

if @action = 'start_background_updateindex'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING AUTO '
EXEC (@execstring)
end

if @action = 'stop_background_updateindex'
begin
-- when table is not existing, we will call ALTER FULLTEXT to raise error for consistent error behavior.
if object_id(@newtabname, 'local') is null OR
ObjectProperty(object_id(@newtabname, 'local'), 'TableHasActiveFulltextIndex') = 0 OR
ObjectProperty(object_id(@newtabname, 'local'), 'TableFullTextBackgroundUpdateIndexOn') != 0
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING MANUAL '
EXEC (@execstring)
end
end

if @action = 'update_index'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START UPDATE POPULATION '
EXEC (@execstring)
end

if @action = 'start_full'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START FULL POPULATION '
EXEC (@execstring)
end

if @action = 'start_incremental'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START INCREMENTAL POPULATION '
EXEC (@execstring)
end

if @action = 'stop'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' STOP POPULATION '
EXEC (@execstring)
end

-- SUCCESS --
return 0

No comments:

Post a Comment

Total Pageviews