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