You may looking for full text search. Please surf the below link.
http://msdn.microsoft.com/en-us/library/ms142571.aspx
Other wise ur solution is at below link.
http://forums.asp.net/t/1663726.aspx/1?keyword+search+from+multiple+tables+sql+query