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:
- 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.
- 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.
Reader Comments
It only works single schema , it does not work with multiple schemas in a database.