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