Application.Configuration_ApplyFullTextIndexing
Documentation | WideWorldImporters |
Schema | Application |
Name | Configuration_ApplyFullTextIndexing |
Applies fulltext indexes to Application.People, Sales.Customers, Purchasing.Suppliers, and Warehouse.StockItems. Replaces Website.SearchForPeople, Website.SearchForSuppliers, Website.SearchForCustomers, Website.SearchForStockItems, Website.SearchForStockItemsByTags with replacement procedures that use fulltext indexing.
Script
CREATE PROCEDURE [Application].Configuration_ApplyFullTextIndexing WITH EXECUTE AS OWNER AS BEGIN IF SERVERPROPERTY(N'IsFullTextInstalled') = 0 BEGIN PRINT N'Warning: Full text options cannot be configured because full text indexing is not installed.'; END ELSE BEGIN -- if full text is installed DECLARE @SQL nvarchar(max) = N''; IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'FTCatalog') BEGIN SET @SQL = N'CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;' EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes AS fti WHERE fti.object_id = OBJECT_ID(N'[Application].People')) BEGIN SET @SQL = N' CREATE FULLTEXT INDEX ON [Application].People (SearchName, CustomFields, OtherLanguages) KEY INDEX PK_Application_People WITH CHANGE_TRACKING AUTO;'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes AS fti WHERE fti.object_id = OBJECT_ID(N'Sales.Customers')) BEGIN SET @SQL = N' CREATE FULLTEXT INDEX ON Sales.Customers (CustomerName) KEY INDEX PK_Sales_Customers WITH CHANGE_TRACKING AUTO;'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes AS fti WHERE fti.object_id = OBJECT_ID(N'Purchasing.Suppliers')) BEGIN SET @SQL = N' CREATE FULLTEXT INDEX ON Purchasing.Suppliers (SupplierName) KEY INDEX PK_Purchasing_Suppliers WITH CHANGE_TRACKING AUTO;'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes AS fti WHERE fti.object_id = OBJECT_ID(N'Warehouse.StockItems')) BEGIN SET @SQL = N'CREATE FULLTEXT INDEX ON Warehouse.StockItems (SearchDetails, CustomFields, Tags) KEY INDEX PK_Warehouse_StockItems WITH CHANGE_TRACKING AUTO;'; EXECUTE (@SQL); END; SET @SQL = N'DROP PROCEDURE IF EXISTS Website.SearchForPeople;'; EXECUTE (@SQL); SET @SQL = N' CREATE PROCEDURE Website.SearchForPeople @SearchText nvarchar(1000), @MaximumRowsToReturn int AS BEGIN SELECT p.PersonID, p.FullName, p.PreferredName, CASE WHEN p.IsSalesperson <> 0 THEN N''Salesperson'' WHEN p.IsEmployee <> 0 THEN N''Employee'' WHEN c.CustomerID IS NOT NULL THEN N''Customer'' WHEN sp.SupplierID IS NOT NULL THEN N''Supplier'' WHEN sa.SupplierID IS NOT NULL THEN N''Supplier'' END AS Relationship, COALESCE(c.CustomerName, sp.SupplierName, sa.SupplierName, N''WWI'') AS Company FROM [Application].People AS p INNER JOIN FREETEXTTABLE([Application].People, SearchName, @SearchText, @MaximumRowsToReturn) AS ft ON p.PersonID = ft.[KEY] LEFT OUTER JOIN Sales.Customers AS c ON c.PrimaryContactPersonID = p.PersonID LEFT OUTER JOIN Purchasing.Suppliers AS sp ON sp.PrimaryContactPersonID = p.PersonID LEFT OUTER JOIN Purchasing.Suppliers AS sa ON sa.AlternateContactPersonID = p.PersonID ORDER BY ft.[RANK] FOR JSON AUTO, ROOT(N''People''); END;'; EXECUTE (@SQL); SET @SQL = N'DROP PROCEDURE IF EXISTS Website.SearchForSuppliers;'; EXECUTE (@SQL); SET @SQL = N' CREATE PROCEDURE Website.SearchForSuppliers @SearchText nvarchar(1000), @MaximumRowsToReturn int AS BEGIN SELECT s.SupplierID, s.SupplierName, c.CityName, s.PhoneNumber, s.FaxNumber , p.FullName AS PrimaryContactFullName, p.PreferredName AS PrimaryContactPreferredName FROM Purchasing.Suppliers AS s INNER JOIN FREETEXTTABLE(Purchasing.Suppliers, SupplierName, @SearchText, @MaximumRowsToReturn) AS ft ON s.SupplierID = ft.[KEY] INNER JOIN [Application].Cities AS c ON s.DeliveryCityID = c.CityID LEFT OUTER JOIN [Application].People AS p ON s.PrimaryContactPersonID = p.PersonID ORDER BY ft.[RANK] FOR JSON AUTO, ROOT(N''Suppliers''); END;'; EXECUTE (@SQL); SET @SQL = N'DROP PROCEDURE IF EXISTS Website.SearchForCustomers;'; EXECUTE (@SQL); SET @SQL = N' CREATE PROCEDURE Website.SearchForCustomers @SearchText nvarchar(1000), @MaximumRowsToReturn int WITH EXECUTE AS OWNER AS BEGIN SELECT c.CustomerID, c.CustomerName, ct.CityName, c.PhoneNumber, c.FaxNumber, p.FullName AS PrimaryContactFullName, p.PreferredName AS PrimaryContactPreferredName FROM Sales.Customers AS c INNER JOIN FREETEXTTABLE(Sales.Customers, CustomerName, @SearchText, @MaximumRowsToReturn) AS ft ON c.CustomerID = ft.[KEY] INNER JOIN [Application].Cities AS ct ON c.DeliveryCityID = ct.CityID LEFT OUTER JOIN [Application].People AS p ON c.PrimaryContactPersonID = p.PersonID ORDER BY ft.[RANK] FOR JSON AUTO, ROOT(N''Customers''); END;'; EXECUTE (@SQL); SET @SQL = N'DROP PROCEDURE IF EXISTS Website.SearchForStockItems;'; EXECUTE (@SQL); SET @SQL = N' CREATE PROCEDURE Website.SearchForStockItems @SearchText nvarchar(1000), @MaximumRowsToReturn int WITH EXECUTE AS OWNER AS BEGIN SELECT si.StockItemID, si.StockItemName FROM Warehouse.StockItems AS si INNER JOIN FREETEXTTABLE(Warehouse.StockItems, SearchDetails, @SearchText, @MaximumRowsToReturn) AS ft ON si.StockItemID = ft.[KEY] ORDER BY ft.[RANK] FOR JSON AUTO, ROOT(N''StockItems''); END;'; EXECUTE (@SQL); SET @SQL = N'DROP PROCEDURE IF EXISTS Website.SearchForStockItemsByTags;'; EXECUTE (@SQL); SET @SQL = N' CREATE PROCEDURE Website.SearchForStockItemsByTags @SearchText nvarchar(1000), @MaximumRowsToReturn int WITH EXECUTE AS OWNER AS BEGIN SELECT si.StockItemID, si.StockItemName FROM Warehouse.StockItems AS si INNER JOIN FREETEXTTABLE(Warehouse.StockItems, Tags, @SearchText, @MaximumRowsToReturn) AS ft ON si.StockItemID = ft.[KEY] ORDER BY ft.[RANK] FOR JSON AUTO, ROOT(N''StockItems''); END;'; EXECUTE (@SQL); PRINT N'Full text successfully enabled'; END; END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51