Find or Search a value in all SQL Server database tables

Sometimes there is a need to search every field of every table for a particular value in SQL Server database. Recently I had a some requirement to search a value against a Microsoft Dynamics AX ERP database and then replace that value with something else. I searched over the internet and found some readymade scripts but those were not exactly satisfying my requirement. I wanted to know the table name, column name, primary key columns and their values, so that I can review it before updating the database tables.

I rewrote a query to match my requirements.  Basically there are 3 important  parameters to narrow down the search, set them correctly prior executing the query.

Note: The below sample script is searching for an exact match of an emailid in the database.
@stringToFind = What is the string/value you are searching?
@dataTypes = What is the datatype of the value you are searching for?
@searcType = Do you want to find the piece of value as a pattern against the columns or exact matching record?

search-value-sql-server-database

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: