|
Dear Team,
i have an requirement that : i want to search all the columns of all the tables in the particular database based on the specific key word or an free text.
example :
table 1: columns data
empname sam
empid 01
table 2 columns data
deptname sam
departmentid 10
table 3 columns data
organization name sam
organization id 1
when i search for text " SAM"
it should search me from the entire database, all tables and columns of it and display the result
output : tablename cloumn value
table1 empname sam
table2 deptname sam
table3 organizationame sam
the example is just an sample not the real data .can this can be happen by using indexes
please help me with sample code or any link related to it .
thanks in advance
|
|
|
|
|
Use system tables. Loop through all the tables based on sysobjects and then loop through each tables columns based on syscolumns. Then try to find the text you want on character columns.
If you're using SQL Server 2005 or above use:
- sys.objects instead of sysobjects
- sys.columns instead of syscolumns
|
|
|
|
|
Thank you very much mika .
i tried that thing but it is taking so much time for execution say for example if i am haiving the database of 400mb it is taking around the 2.25 min for executing.. is there any alternative way for that
|
|
|
|
|
No there's no direct alternative as far as I know. Whta you could do is to create a new keyword table where you store keywords and references to the rows and tables containing them. Kind of an index, if you like.
|
|
|
|
|
Dear Team,
Please help me out by giving any related link or code . for those kind of search .
thanx in advance .
|
|
|
|
|
Mika has told you how to do it, and he has also suggested a good alternative, build a keyword table for indexing.
What else do you expect, someone to do it for you?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi bob,
i know the solution that mika has provided is very good .but we dont know what the user will type and want to search there is no constant keywords predefined so that i can create them as separate table and provide the index.
the user will just type any keyword/word in the text box from the frontend he required and it should search all the tables and columns of the table in the database and i have to show this in the front ent in the table format. here the user will analyse the information based on the searched records .
It is just like the google search we does( type the keyword in free text) it will display the result.
but here it is mandatory for me to search all the table and columns. here different users will update data in different tables . so in order to analyze the data i have to go through all the tables and columns .
it is just like we are investigating and analyzing some thing based on the input string and output it display( just like linking from one record to another record and analyzing and making decisions etc this is based on pure bussiness logic).
thanks in advance and sorry for trouble
|
|
|
|
|
If you have to search every column of every table then you will just have to accept the poor performance as there is no magic way of making whst is basically a set of unindexed queries run fast.
You may be able to speed it up by generating sql for each table which excludes columns you know cannot be searched (identity columns for example would be meaningless), but maybe you should be looking at caching in a data access layer?
Hope this maybe helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: You may be able to speed it up by generating sql for each table which excludes columns you know cannot be searched (identity columns for example would be meaningless),
One way could be creating an indexed view on the varchar columns, but if I remember correctly Enterprise Edition is needed for that.
Ashfield wrote: maybe you should be looking at caching in a data access layer
That may be the only option if performance cannot be accepted and still everything needs to be searched...
|
|
|
|
|
shaik abdul gani wrote: we dont know what the user will type and want to search there is no constant keywords predefined
Then why don't you build the index for all words? When adding or updating the data in the database, check all varchar columns and separate all words from them. After that, add them to your index table(s) and apply references to the row containing the data.
If you want to do this in the db, I think it's just modelling question.
Another good approach is the one Bob provided if you can take this to application level.
|
|
|
|
|
thanx a lot for all of you for giving the views and suggestions . thanx a lot once again
|
|
|
|
|
You may take a look at Full Text Search, you could mix the strategy that the fellow developer has pointed before (scanning the tables names in the sys.objects table) plus querying the tables using FTS.
Google is your friend, you could find a lot of info about FTS.
Hope this helps.
|
|
|
|
|
good evening,
i can create a form ,i want to store data in database through form,but two textboxes values store in one column ,the controls names are
idproof(combobox),others(textbox).combobox have some values including "others",if we select any value except others, the textbox won't display but we can select others then textbox display and enter the value.my problem is,i want to the data in sqlserver database but i can use only one column to both controls,how can i store two controls datain one column but each time one control value is fired.
give me a solution to my problem. thank u for providing this site.
|
|
|
|
|
Lets see the code
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Vuyiswa Maseko wrote: Lets see the code
Don't be silly. If he had the code, he'd pass in his homework.
Jon
Smith & Wesson: The original point and click interface
|
|
|
|
|
Good Afternoon Guys
i have the Following Query
SELECT *
INTO #Rows
FROM OPENXML ( @doc , '/Timetable/Rows' , 2)
WITH ( ID int,
Descr varchar(64),
StartTime smalldatetime,
Duration int,
Offs varchar(64)
)
I understand the First Part
SELECT *
INTO #Rows
FROM OPENXML ( @doc , '/Timetable/Rows' , 2)
but i dont understand Why With ? can someone Explain what is With Doig with Columns
Thank you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
the WITH is effectively giving you the table definition. SQL cannot work out datatype etc from xml, so you need to supply a mapping.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks man, it make sense
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thats OK, you're welcome
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I had created a Stored Procedure.Its created successfully.I had written Cursor in that.
I had mentioned some of the code below:-
-----------
DECLARE InsertLog CURSOR local
FOR SELECT @ColumnName FROM [@TableName] WHERE @PrimaryColumn=@PrimaryValue
FOR READ ONLY
OPEN InsertLog
FETCH NEXT FROM InsertLog into @ColumnValue
----------
I am passing values like ColumnName TableName and where condition column and values as parameter
When I pass value to it gives an error
Invalid object name '@TableName'.
Can any body tell me why this error is coming.
It's urgent
|
|
|
|
|
I could be totally wrong here as I'm not a database expert but, @TableName is a parameter therefore it should not be enclosed in []. Try removing the [] from around @TableName.
|
|
|
|
|
Thanks for reply,
But if I try to remove it,it gives an error,
Must declare the table variable "@TableName".
|
|
|
|
|
Well, have you declared the @TableName parameter?
|
|
|
|
|
|
Show us the Whole Code
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
