Database

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

14-Dec-08 21:34

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

shaik abdul gani14-Dec-08 22:06

shaik abdul gani

14-Dec-08 22:06

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

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

Ashfield15-Dec-08 1:37

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

15-Dec-08 1:37

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

Wendelius15-Dec-08 5:30

Wendelius

15-Dec-08 5:30

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...

The need to optimize rises from a bad design.My articles[^]

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

Wendelius15-Dec-08 5:26

Wendelius

15-Dec-08 5:26

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.

The need to optimize rises from a bad design.My articles[^]

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

shaik abdul gani15-Dec-08 23:53

shaik abdul gani

15-Dec-08 23:53

thanx a lot for all of you for giving the views and suggestions . thanx a lot once again

Re: How to search all columns of all tables in a database for a keyword? ( based on indexes)

emiaj15-Dec-08 5:31

emiaj

15-Dec-08 5:31

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.

Jaime Febres
The worst blog in the world

Insertion

kirandilip12-Dec-08 0:36

kirandilip

12-Dec-08 0:36

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.

Re: Insertion

Vimalsoft(Pty) Ltd12-Dec-08 0:59

SQL Question Statement "With"

12-Dec-08 0:59

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

Re: Insertion

Oakman12-Dec-08 2:14

Oakman

12-Dec-08 2:14

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

Vimalsoft(Pty) Ltd11-Dec-08 23:02

Re: SQL Question Statement "With"

11-Dec-08 23:02

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

Ashfield12-Dec-08 1:19

Re: SQL Question Statement "With"

12-Dec-08 1:19

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

Vimalsoft(Pty) Ltd12-Dec-08 1:22

Re: SQL Question Statement "With"

12-Dec-08 1:22

Thanks man, it make sense

Ashfield12-Dec-08 3:59

12-Dec-08 3:59

Thats OK, you're welcome

Bob
Ashfield Consultants Ltd

Problem Regarding Cursor

ais0711-Dec-08 22:56

ais07

11-Dec-08 22:56

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

Jay Royall11-Dec-08 23:34

Jay Royall

11-Dec-08 23:34

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.

ais0711-Dec-08 23:39

ais07

11-Dec-08 23:39

Thanks for reply,
But if I try to remove it,it gives an error,

Must declare the table variable "@TableName".

Jay Royall11-Dec-08 23:46

Jay Royall

11-Dec-08 23:46

Well, have you declared the @TableName parameter?

ais0711-Dec-08 23:48

ais07

11-Dec-08 23:48

Yes.....

Vimalsoft(Pty) Ltd12-Dec-08 1:10

12-Dec-08 1:10

Show us the Whole Code

Ashfield12-Dec-08 1:21

12-Dec-08 1:21

You cannot do a select from a variable unless the varaible is a table data type. The same applies to @ColumnName.

Bob
Ashfield Consultants Ltd