Last month during the Steel City SQL User Group meeting, I was having a discussion with other group members around Productivity Improvements while working in SQL Server Management Studio IDE on a day to day basis.
Personally I am a huge advocate of anything that helps with productivity – tools that helps you being more efficient. At the same time, being aware of tips and tricks which could help you save even 10 minutes of your time while working on your regular stuffs plays a significant role. In the grand scheme of things and over a period of time, this 10-15 minute saving per day can be detrimental to your overall success as a developer. I am sure no one wants to do a manual task over and over again – if there are better and more efficient ways of doing it, you need to invest in it & help yourself in being more productive.
Think about a scenario when you want to update an address which is displayed in the User Interface. The application and database is totally new to you, so you manually try to identify the table in which the address might be stored, and update it – however the modified address does not reflect in the UI. This indicates that the UI might be pulling the address from a different table and column combination. Which is the easiest way to figure out all tables/columns in your database that contains the address information you are looking for?
So the question is, how do you search for a particular value/data in your SQL Database?
Be default, SSMS does not provide any tools/utility to search for a value/data inside a particular database. This kind of surprises me a bit, since it is a needed functionality and would be a very popular feature to be built into the SSMS IDE.
All these while, I had a large T-SQL query with me which I used to execute to search for that ‘value’ in all columns of all tables in the database.
I started to browse through internet to find out some free add-ins or tools for SQL Server Management Studio IDE which could help in this scenario. I bumped into one such tool, which I found very helpful and wanted to share with all of you in this article.
ApexSQL Search is an awesome tool which can help you search data in your database in a single click. It abstracts the entire complexity of running a complicated T-SQL query and its related maintenance.
You can download this free tool and learn more about its features here –
ApexSQL Search provides a bunch of features –
- Search for SQL objects
- Quickly find data in SQL tables
- Visualize relationships between objects
- Edit extended properties
- Easily navigate to result objects
- Rename SQL objects safely
However for the purpose of this article, I will be only focusing on the data search functionality.
Once you install the tool, you will see a new tab named as ‘ApexSQL’ in your toolbar.
The Database Text Search functionality provided by ApexSQL Search allows you to search text, dates, numbers and even unique identifiers stored in the tables/views in your database.
Click on the option ‘Database text search’ as shown above, type in the Search text and click on the ‘Find now’ button to get the results.
There are additional options to customize your search for more precise results. You can do that by checking the required check-boxes as shown in the screenshot below.
ApexSQL Search is a great addition to your SSMS IDE and can save you lot of time in writing queries to search for data in your SQL tables. I would highly recommend you to download this free tool and start leveraging its productivity benefits.
In my upcoming posts, I will be evaluating more of such available tools and will be sharing useful tips and tricks to boost efficiency.
Follow up blog from a performance standpoint –
Searching Data in SQL Tables – Impact on Performance
Categories: SQL Server, SQL Tips
This is a feature I will never use due to performance issues and potential risk of still missing important application logic.
To achieve the goal I would first identify all frontend scenarios where the wrong value would apear and I would use SQL Profiler to identify the full query which is sent to the SQL backend. This would pin point the exact database.table.column.row to change. Dont forget that in some scenarios the value you are searching for might not be in a single database. I’ve seen applications that join tables from diffrent databases on the same or diffrent SQL Server.
In conclusion if you want to be the engineer that does precise/science work, use SQL Profiler.
LikeLiked by 1 person
Thanks for your feedback Rares. I posted a follow up blog from a performance standpoint.
Do check it out –