Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Microsoft SQL Server Installation

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.