Q

Web site that searches in specified tables and columns

Monitoring and Administration expert Kevin Kline discusses how to create a 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.

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

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close