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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top