Ask the Expert

Web site that searches in specified tables and columns

We want to enable a search on our Web site that goes into the SQL Server database and searches in specified tables and columns. The search should be of specific text, and it should return results for all text matches. For example, a user from a secured Web site enters text "SQLquery" and it should go to SQL server pre-specified tables and return results.

    Requires Free Membership to View

Unfortunately, I can't answer this question without more details. Do you need the search to span only VARCHAR columns, only TEXT columns, or both VARCHAR and TEXT columns?

Assuming that you mean only VARCHAR datatype columns, you could create a stored procedure to use nested cursors to cycle through all users databases, and within those databases, the user tables and even the VARCHAR columns of the table, performing the character string search. SQL Server 2000 contains system stored procedures called ms_foreachdb and ms_foreachtable that perform a specific activity on each database or table, respectively.

If you're running SQL Server 7, or would like a somewhat faster version of the same procedures for SQL Server 2000, you can find scripts for sp_foreachdb and sp_foreachtable at SQLServerCentral.com

 


Do you have comments on this Ask the Expert Q&A? Let us know.

This was first published in September 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: