Searching for a string in the entire db

appsDBA

Hi,
I am very new to red-gate and still assessing the products from the 14 day trial.  I have a sql server, 2014 and I need to find what tables and how many rows contain a particular IP address.  Let's say 192.168.6.1.  I want to search my MS SQL 2014 server for all occurences of "192.168.6.1".  I need to know all the tables in which that string exists.
My first roadmap was to peform a back up and take the 'bak' file and convert it to a 'MySQL' database.  Reason being that searching for the string with MySQL is easy with phpmyadmin.  This would have given me all the tables.  Given red-gates suite of products.  What should I use to accomplish my task?  I would like all the options possible.  I have tried using 'Rebasedata' and it failed to convert from Ms SQL to MySQL.  So now I'm at red-gate.

Jon_Kirkwood

Hi @appsDBA

Thank you for reaching out on the Redgate forums with your question on text searching.

I think SQL Search is the closest tool we would offer - however it only searches the metadata of your database objects and not the actual data itself.

 

My recommendation would be to create a stored procedure which will do this for you. There are a few examples online but here is one for reference:

https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

 

You would create a procedure in your sql server environment, then run the procedure with the search string you want to return ("192.168.6.1"). It would then output any table value containing that string.

Hope this is useful for your task.

appsDBA

@Jon_Kirkwood much thanks for your assitance.  The link at the bottom to search all tables in the DB worked.  This is the link that had the solution: https://vyaskn.tripod.com/search_all_columns_in_all_tables.htm.  Pity MS SQL is not as flexible as MySQL.  In MySQL this is easily done.  Thanks again.