June 13, 2009 at 10:30 am
All,
I have more than 300 tables in my DB , each table as a unique column, this column value (GUID) is unique across all the tables (entire DB). I want to create only one fulltext catalog and add all the columns from all the tables. Then, I want to search the catalog and get the GUID (key). I tried to find out a way to search all the tables using containstable but I had to join more than 256 tables. Is there any way to search the entire catalog without specifying the table and get the key and rank column ?.
Any suggestions ?.
thanks,
June 13, 2009 at 1:40 pm
I'm really curious why you want to do that? Sounds like very special requirement.
If you want to search in different tables the usual way to do this are VIEWs which combine those tables or a dynamic SQL approach.
What should be the benefit of one huge full text index monster instead of many small bugs? 😀
June 13, 2009 at 6:33 pm
Thank you for your reply,
We wanted to implement site search for our web app product. scenario is, Users can search for all the objects (eg, users, groups, companies,files, tasks, emails, email folders,messages, threads, replies bla bla .. >300 blas 😉 ) in the system. so what my thought is that, I would create a single text catalog , add all the tables and columns for indexing and search the text catalog to get keys, then display the top 100 records in the result. When I tried to create view to union all the tables, it throws an error "too many tables in the query". Please help me to solve this. I got one more question.. Is it possible to add "datetime' and "money" column types to text catalog?. When i tried to add, it throws an error "column type not supported for fulltext indexing". :(. got stuck with these 2 issues. any suggestions?
thank you,
June 14, 2009 at 1:32 am
I published a script here some month ago which might help you to join all those blahs :-D:
http://www.sqlservercentral.com/scripts/anywhere/66000/
The created dynamic SQL is for usual (non full-text) search but it should be no big deal to change the LIKE-part to a CONTAINS syntax.
It's not possible to add numeric/datetime columns to a full-text index. For this you have to determine if the search pattern is numberic or datetime, if yes search this columns otherwise do not. You can use the build-in functions ISNUMERIC() and ISDATE() to determine the type.
Flo
June 14, 2009 at 2:29 pm
Thank you, I think I will go with dynamic queries.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply