spCreateContentSearchFullTextIndex

CREATE proc spCreateContentSearchFullTextIndex
as
begin
  if fulltextserviceproperty('IsFulltextInstalled') = 1
  begin
      if not exists (select * from sys.fulltext_catalogs where name = 'CONTENT_SEARCH')
          CREATE FULLTEXT CATALOG [CONTENT_SEARCH] WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo]

      -- Adding full text index for PARTICIPANTEMAILTEMPLATE SUBJECT and CONTENTHTML columns

      if OBJECTPROPERTY(OBJECT_ID('[dbo].PARTICIPANTEMAILTEMPLATE'), 'TableHasActiveFulltextIndex') = 0
      begin
          CREATE FULLTEXT INDEX ON [dbo].PARTICIPANTEMAILTEMPLATE (SUBJECT, CONTENTHTML)
          KEY INDEX PK_PARTICIPANTEMAILTEMPLATE 
              ON ([CONTENT_SEARCH]) 
              WITH (CHANGE_TRACKING AUTO, STOPLIST OFF)
      end

      -- Adding full text index for EMAILTEMPLATE SUBJECT and CONTENTHTML columns

      if OBJECTPROPERTY(OBJECT_ID('[dbo].EMAILTEMPLATE'), 'TableHasActiveFulltextIndex') = 0
      begin
          CREATE FULLTEXT INDEX ON [dbo].EMAILTEMPLATE (SUBJECT, CONTENTHTML)
          KEY INDEX PK_EmailTemplate
              ON ([CONTENT_SEARCH]) 
              WITH (CHANGE_TRACKING AUTO, STOPLIST OFF)
      end
  end
end