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