October 2, 2013 at 9:57 am
Hi,
I came across a FULLTEXT catalog that documents tables in a database.
The catalog has 5 descriptions tables (table, columns, triggers, foreign keys and check constraints).
If I want to search for Sales and Product the SalesDetails table doesn't show on the results cause it's being built like this:
SELECT t0.Id, ct.Rank FROM CONTAINSTABLE(DescriptionsTables, *, '"*sales*" AND "*products*"') ct INNER JOIN DescriptionsTables t0 ON tc.[Key] = t0.Id
UNION ALL
SELECT t0.TableId, ct.Rank FROM CONTAINSTABLE(DescriptionsTablesColumns, *, '"*sales*" AND "*products*"') ct INNER JOIN DescriptionsTablesColumns t0 ON tc.[Key] = t0.Id
.....
Since the text "sales" is only in tables descriptions and "products" in tables columns descriptions, never on both at the same time, the table doesn't show on the results...
There is a way, that I know of, to make this work... Create a table whenever DescriptionsTables is changed (trigger) and populate it with the several tables' data and use it for the fulltext instead of the other 5....
But is there an easy way to do this? Merge all the catalogs in just one for the search?! (probably stupid question since the reference for the KEY column is different on all the catalog tables...). Can I create a view with all the tables information (all 5 tables) and add it to the fulltext catalog?
Thanks,
Pedro
October 2, 2013 at 10:36 am
I tried creating a view
CREATE VIEW DatabaseDescription_TablesView
with schemabinding
AS
SELECT
Id,
Description,
TableName,
(SELECT ColumnName + '; ' + Description + ';' FROM dbo.DatabaseDescription_TableColumns tc WHERE tc.TableID = t.Id FOR XML PATH('')) Columns,
(SELECT IndexName + '; ' + Columns + '; ' + obs + ';' FROM dbo.DatabaseDescription_TableIndexes ti WHERE ti.TableID = t.Id FOR XML PATH('')) Indexes,
(SELECT ConstraintName + '; ' + ReferencedTable + '; ' + ReferencedColumns+ '; ' + OriginalColumns+ '; ' + Obs + '; ' FROM dbo.DatabaseDescription_TableForeignKeys FOR XML PATH('')) FKs,
(SELECT ConstraintName + '; ' + Expression + '; ' + Obs + '; ' FROM dbo.DatabaseDescription_TableCheckConstraints FOR XML PATH('')) Constraints
FROM dbo.DatabaseDescription_Tables t WHERE TempDB=0
GO
create unique clustered index IDX_DatabaseDescription_TablesView on dbo.DatabaseDescription_TablesView (id)
GO
so the table information would be in one single row and "table" but I can not create an index on a view with subqueries... 🙁
I also tried to remove the subquery and create a function but no indexes on views with either APPLY or multi table value functions...
Pedro
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply