spCreateConstituentSearchFullTextIndex

CREATE proc spCreateConstituentSearchFullTextIndex
as
begin
    if not exists (select * from sys.fulltext_catalogs where name = 'CONSTITUENT_SEARCH')
        CREATE FULLTEXT CATALOG [CONSTITUENT_SEARCH] WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo];

    -- Adding full text index for CONSTITUENT NAME column

    IF OBJECTPROPERTY(OBJECT_ID('[dbo].CONSTITUENT'), 'TableHasActiveFulltextIndex') = 0
    begin
        CREATE FULLTEXT INDEX ON [dbo].CONSTITUENT KEY INDEX PK_CONSTITUENT
            ON ([CONSTITUENT_SEARCH]) WITH (CHANGE_TRACKING AUTO)
        ALTER FULLTEXT INDEX ON [dbo].CONSTITUENT ADD ([NAME])
        ALTER FULLTEXT INDEX ON [dbo].CONSTITUENT ENABLE
        ALTER FULLTEXT INDEX ON [dbo].CONSTITUENT SET STOPLIST OFF
    end
end