April 22, 2012

sp_fulltext_database (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_database(varchar @action)

MetaData:

 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 ((SELECT is_read_only FROM sys.databases WHERE name=@dbname) = 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 DATABASEPROPERTYEX(@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 DATABASEPROPERTYEX(@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

No comments:

Post a Comment

Total Pageviews