Today I have to work on full-text indexing to do a bit of searching, I found this usefull little Article[^] that shows you how to create the index with scripts.
Here is what I came up with:
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'search', 'create'
go
sp_fulltext_table 'Question', 'create', 'search', 'PK_Question'
go
sp_fulltext_column 'Question', 'Title', 'add'
go
sp_fulltext_column 'Question', 'Question', 'add'
go
sp_fulltext_table 'Question', 'activate'
go
sp_fulltext_catalog 'search', 'start_full'
go
This is my attempt to also script the scheduled full index:
use master
exec msdb..sp_delete_job @job_name = 'Start_Full on Alexandria.Search.'
declare @id BINARY(16)
exec msdb..sp_add_job @job_name = 'Start_Full on Alexandria.Search.',
@description = 'Scheduled full-text full population for
full-text Catalog Search in database Alexandria.
This job was created by the full-text scheduling dialog or
full-text index wizard..',
@enabled = 1,
@start_step_id = 1,
@notify_level_eventlog = 2,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@category_name = 'Full-Text',
@job_id = @id OUTPUT select @id
exec msdb..sp_add_jobstep @job_id = @id,
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@flags = 0,
@step_name = 'Full-Text Indexing',
@subsystem = 'TSQL',
@command = 'use [Alexandria] exec sp_fulltext_catalog ''Search'', ''start_full''',
@database_name = 'master'
exec msdb..sp_add_jobserver @job_id = @id,
@server_name = '(local)'
exec msdb..sp_add_jobschedule @job_id = @id,
@name = 'complete',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20060713,
@active_end_date = 99991231,
@active_start_time = 120000,
@active_end_time = 235959
exec msdb.dbo.sp_update_job @job_id = @id,
@automatic_post = 0 ,
@owner_login_name = 'sa'
exec msdb..sp_help_job @job_id = @id, @job_aspect = N'job'
exec msdb..sp_help_jobstep @job_id = @id
exec msdb..sp_help_jobschedule @job_id = @id
|