-- Löschen Volltextindex und Volltextkatalog [PROCAD]
-- [UT] [MST] 23.11.2018 --


CREATE TABLE [customer].[DeleteFullTextCatalog]
([SQLSTAT] [varchar](100) NULL,
	[rowID] [int]
	)
ON [PRIMARY]
GO


INSERT INTO [customer].[DeleteFullTextCatalog]
           ([SQLSTAT]
           ,[rowID])
select t2.SQLSTAT,ROW_NUMBER() OVER (order by [SQLSTAT] ) as LINEID from
(select *
from
(select 'DROP FULLTEXT  INDEX ON ' +t.TableName +';' as SQLSTAT
from
(SELECT  CatalogName = QUOTENAME(cat.name), 
            TableName = QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name)
    FROM sys.fulltext_indexes ix
    INNER JOIN sys.fulltext_catalogs cat ON cat.fulltext_catalog_id = ix.fulltext_catalog_id
    INNER JOIN sys.fulltext_index_columns ixc ON ix.object_id = ixc.object_id
    INNER JOIN sys.tables tbl ON tbl.object_id = ix.object_id
    INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id
    INNER JOIN sys.columns col ON ixc.object_id = col.object_id AND ixc.column_id = col.column_id
    INNER JOIN sys.indexes pk ON ix.object_id = pk.object_id AND ix.unique_index_id = pk.index_id
		where QUOTENAME(cat.name)='[PROCAD]'
		group by QUOTENAME(cat.name), 
            QUOTENAME(sch.name) + '.' + QUOTENAME(tbl.name)
			) t
			
		) t1
		UNION
	SELECT 'DROP FULLTEXT CATALOG [PROCAD];' as SQLSTAT
	
		) t2
GO


DECLARE Delete_Cursor CURSOR FAST_FORWARD
FOR  
SELECT SQLSTAT
FROM [customer].[DeleteFullTextCatalog]
ORDER BY rowID;  
DECLARE @SQLString nvarchar(500); 

OPEN Delete_Cursor;  
FETCH NEXT FROM Delete_Cursor
INTO @SQLString;  

WHILE @@FETCH_STATUS = 0  
   BEGIN  
	EXECUTE sp_executesql @SQLString;
	DELETE from [customer].[DeleteFullTextCatalog] where SQLSTAT=	@SQLString;
      FETCH NEXT FROM Delete_Cursor
		INTO @SQLString;  
   END;  

CLOSE Delete_Cursor;  
DEALLOCATE Delete_Cursor;  
GO  

DROP TABLE [customer].[DeleteFullTextCatalog]
GO