Search all columns of all tables for a particular value

Several times I have came across with the question that how can I search all columns of all tables in a SQL Server database for a particular value. Unfortunately, you can not perform this query directly by any already provided system table or object. 

There are some fundamental points to remember:

 

1. You have to iterate the list of tables and for each table you have to iterate each column and perform an exist query against it. 
2. You will need three types of queries due to the data types i.e. string, number and date and time.

I have found an excellent article here which provides a good explanation along with the necessary sql script. I would recommend every reader to read this article.