Recently I have written a small search utility to search the text contain in SQL database. This utility was divided into three major parts. One important trick to avoid the above error is as follow:
1. Retrieving all the schemas’ and tables’ names (schema name cannot be omitted or else the third part of it will have numerous errors).
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
2. Retrieving all the information column name with any type of char data types
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘" + strSchema + "’ AND TABLE_NAME= ‘" +strTable + "’ AND DATA_TYPE LIKE ‘%c%’
3. Find the text from all the columns with any char data type.
SELECT * FROM " + strSchema + "." + strTable + " WHERE [" + strCol + "] LIKE ‘%" + strFind + "%’
Due to the database that can be very big in size hence you might get the error “The timeout period elapsed prior to completion of the operation or the server is not responding” with the default 30 seconds timeout setting. I change the value to 240.
SqlCommand cmd = new SqlCommand(strSQL, connText);
cmd.CommandTimeout = 240;
Please download the sample from http://code.msdn.microsoft.com/SQLUtilities. Please feedback if you discover any bug.
Minimum requirement: MS SQL 2005 Express, Visual C# 2008 Express