Searching the Databse. 1

[EPiXNiCROS]

Does anybody know of a way to search the entire database or table for a string of text?


Thank you
Chris s. Raver

 

[JMCraig]

There's a number of approaches to this rather conceptually large problem. To search a given table and column for a string of text, you can use the LIKE operator in your WHERE clause. I'm assuming you don't mean this. (If this sounds interesting, check out the on-line info in the FAQ "Where can I read about ...?"; you can search the on-line manuals for "pattern matching" (I recommend the Transact-SQL Users Guide as a good starting point for learning about LIKE.)

Other options you might want to investigate include the "Full Text Search Specialty Data Store" option.

Depending upon your application, you might also consider creating what are sometimes called "index tables." These are tables that contain (potentially somewhat changed) copies of data that is found elsewhere, but have the information organized to support a different type of search. This is a way to, in effect, have more than one clustered index on a table. For example, you might have a set of personnel records that are organized by employee number, but there's a separate table you can join on that's organized by employee name (you could even have two of these: one ordered by last, first names, another by first last names).

HTH,

JMC J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[EPiXNiCROS]

The Problem.

I have a string of text somewhere in the database. I’m not sure what table or column the data resides in. There are several hundred tables in this database so it is impossible to go through every table individually and search for the text. The only thing I have is the string of text. If there was a way I could search the entire database for that text it would work for me.

 

[EPiXNiCROS]

The Problem.

I have a string of text somewhere in the database. I’m not sure what table or column the data resides in. There are several hundred tables in this database so it is impossible to go through every table individually and search for the text. The only thing I have is the string of text. If there was a way I could search the entire database for that text it would work for me.


Thank you

Chris s. Raver

 

[JMCraig]

Chris,

Sorry not to have gotten back to you sooner.

Hmm. Interesting problem. Is this something that happens a lot or is this an exceptional situation?

If it's an exceptional situation, then I can imagine creating a series of SQL statements to check all of your tables. Create the statements by examining all the columns (using the syscolumns table) that are character-type columns. Something like this:

select "select * from " + object_name (id)
+ " where " + name " like '%/* your string here */%'"
from syscolumns
where type in (39 /* varchar */, 47 /* char */, 35 /* text */)

This would give you a series of select statements which would mostly return no rows when executed.

If this isn't an exceptional circumstance, then it sounds like you should look into some type of indexing scheme involving either the full-text searching speciality data store or index tables, or a combination of both.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net