go backarticles

Articles of SQLschool.gr Team

Scripting Full Text Search Indexes

Antonios Chatzipavlis





Presentation Code

create or alter proc sqlschool_sp_fulltext_scripting
as
begin

    set nocount on;

    declare @lf char(2) = char(13)+char(10);
    declare @tab char(1) =char(9);
    declare @fts_script table (line nvarchar(4000));

    ---- catalogs

    declare catalogs cursor read_only for 
    select fulltext_catalog_id,name,is_default,is_accent_sensitivity_on,principal_id
    from sys.fulltext_catalogs
    order by fulltext_catalog_id;

    declare @fulltext_catalog_id int ,
            @name sysname,
            @is_default bit,
            @is_accent_sensitivity_on bit,
            @principal_id int;

    open catalogs;

    insert into @fts_script(line) values ( N'use '+ QUOTENAME(db_name())+';'+@lf ) ;
    insert into @fts_script(line) values ( N'GO' + @lf );

    fetch next from catalogs into @fulltext_catalog_id,@name,@is_default,@is_accent_sensitivity_on,@principal_id;
    while (@@fetch_status <> -1)
    begin
        if (@@fetch_status <> -2)
        begin

            insert into @fts_script(line) values ( N'--'+@lf ) ;
            insert into @fts_script(line) values (N'--'+@tab + N'FULL TEXT CATALOG '+@name + @lf);
            insert into @fts_script(line) values (N'--'+@lf);
            insert into @fts_script(line) values (@lf);

            insert into @fts_script(line) values (N'CREATE FULLTEXT CATALOG ' + quotename(@name) + @lf);
            insert into @fts_script(line) values (N'WITH ACCENT_SENSITIVITY = ' + case @is_default when 1 then N'ON' else N'OFF' end + @lf);
            if @is_default = 1    
                insert into @fts_script(line) values ( N'AS DEFAULT' + @lf);

            insert into @fts_script(line) values (N'AUTHORIZATION ' +  quotename( (select name from sys.database_principals where principal_id = @principal_id) ) + ';'+@lf);

            insert into @fts_script(line) values ( N'GO' + @lf );

            insert into @fts_script(line) values ( @lf );

        end;

        fetch next from catalogs into @fulltext_catalog_id,@name,@is_default,@is_accent_sensitivity_on,@principal_id;

    end;

    close catalogs;
    deallocate catalogs;

    ---- indexes

    declare fts_indexes cursor read_only for 
    select object_id,fulltext_catalog_id,change_tracking_state_desc,stoplist_id,property_list_id,unique_index_id,data_space_id
    from sys.fulltext_indexes
    order by fulltext_catalog_id ;


    declare @object_id int ,
            @index_catalog_id int,@index_catalog_id_helper int = 0,
            @table_name sysname,
            @schema_name sysname,
            @change_tracking_state_desc nvarchar(60),
            @stoplist_id int, @stoplist_name sysname,
            @property_list_id int,@property_list_name sysname,
            @unique_index_id int,@unique_index_name sysname,
            @data_space_id int;

    open fts_indexes;

    fetch next from fts_indexes into @object_id,@index_catalog_id,@change_tracking_state_desc,@stoplist_id,@property_list_id,@unique_index_id,@data_space_id;
    while (@@fetch_status <> -1)
    begin
        if (@@fetch_status <> -2)
        begin

            if @index_catalog_id_helper <> @index_catalog_id
            begin
                insert into @fts_script(line) values (N'--'+@lf);
                insert into @fts_script(line) values (N'--'+@tab + N'INDEXES OF FULL TEXT CATALOG '+(select name from sys.fulltext_catalogs where fulltext_catalog_id = @index_catalog_id)+ @lf);
                insert into @fts_script(line) values (N'--'+@lf);
                insert into @fts_script(line) values (@lf);
                set @index_catalog_id_helper = @index_catalog_id;
            end

            select @table_name=name,@schema_name= schema_name(schema_id) 
            from sys.tables 
            where object_id = @object_id
        
            insert into @fts_script(line) values ( N'--'+@tab + N'FULL TEXT INDEX ON TABLE '+@schema_name+'.'+@table_name   + @lf);
            insert into @fts_script(line) values (@lf);

            insert into @fts_script(line) values ( N'CREATE FULLTEXT INDEX ON '+ QUOTENAME(@schema_name)+'.'+QUOTENAME(@table_name)+ @lf);
            insert into @fts_script(line) values (N'('+ @lf);
        
            declare @cols nvarchar(max) =''
            select @cols+=N','+@tab+QUOTENAME(c.name)+
                        case when (select name from sys.columns where object_id = @object_id and column_id = type_column_id) is not null
                                then N' TYPE COLUMN '+ (select QUOTENAME(name) from sys.columns where object_id = @object_id and column_id = type_column_id)
                            else '' end+
                        N' LANGUAGE '+ cast(fc.language_id as nvarchar(20)) + 
                        case fc.statistical_semantics when 1 then ' STATISTICAL_SEMANTICS ' else '' end + @lf --, type_column_id,c.name 
            from sys.fulltext_index_columns as fc
            inner join sys.columns as c on c.object_id = fc.object_id and c.column_id = fc.column_id
            where fc.object_id = @object_id;

            insert into @fts_script(line) values (stuff(@cols,1,1,''))
            insert into @fts_script(line) values (N')'+ @lf);
        
            select @unique_index_name=name from sys.indexes where index_id=@unique_index_id and object_id = @object_id
            insert into @fts_script(line) values (N'KEY INDEX '+ QUOTENAME(@unique_index_name) + @lf);
        
            insert into @fts_script(line) values ( N'ON ('+(select quotename(name)  from sys.fulltext_catalogs where fulltext_catalog_id = @index_catalog_id)+N','+' FILEGROUP ' + QUOTENAME( FILEGROUP_NAME(@data_space_id)) + N')'+ @lf);
        
            insert into @fts_script(line) values ( N'WITH'+ @lf);
        
            insert into @fts_script(line) values (N'('+ @lf);
            insert into @fts_script(line) values (@tab + N'CHANGE_TRACKING = ' +  @change_tracking_state_desc + @lf);
        
            select @stoplist_name=name from sys.fulltext_stoplists where stoplist_id = @stoplist_id
            if len(@stoplist_name) > 0  
                insert into @fts_script(line) values (N','+@tab + N'STOPLIST = '+ @stoplist_name+ @lf);
            else 
                insert into @fts_script(line) values (N','+@tab + N'STOPLIST = SYSTEM'+  @lf);
        
            SELECT @property_list_name=name FROM sys.registered_search_property_lists where property_list_id=@property_list_id;  
            if len(@property_list_name)>0 
                insert into @fts_script(line) values (N','+@tab + N'SEARCH PROPERTY LIST = '+QUOTENAME(@property_list_name)+ @lf);
        
            insert into @fts_script(line) values (N');'+ @lf);
            insert into @fts_script(line) values (N'GO' + @lf);
            insert into @fts_script(line) values (@lf);
            
        end
            
        fetch next from fts_indexes into @object_id,@index_catalog_id,@change_tracking_state_desc,@stoplist_id,@property_list_id,@unique_index_id,@data_space_id;
    end;

    close fts_indexes;
    deallocate fts_indexes;
---
    select * from @fts_script
end
go


Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


PASS chapter logo

The Official PASS Local Group for Greece

1432 33 595 27 39 1317
sql school greece logo
© 2010-2019 All rights reserved