The Third Mission
In this installment of SQLCoOp, we are sharing our views on the newly released SQL Search 2.0 product by Red Gate. This amazing product is free to the public and can be downloaded here.
SQL Search is one of those amazing products that you just can’t believe they give away. While I always find it useful, I found it the most useful this year when I was getting to know a new (to me) database. Why? Because I could quickly and easily search all the databases for particular keywords. Below I’ve included some of my favorite Use Cases for using SQL Search.
Use Case 1: Where did I leave my TODO list?
One of the things I love to do is leave notes for myself in my SQL objects, but I’m not always good at remembering where those notes are. With SQL Search, I can find them easily by searching for the keyword TODO. Now I can go a step further and look for all the TODO’s that ALSO have my name.
I do this by using the AND operator to search for the two keywords todo and mstuewe. There are some special rules to note.
1. AND and it’s counter part OR are both case sensitive.
2. You can only use one at a time. I can have todo AND mtuewe AND 2014, but I can’t have todo AND (mstuewe OR mickey). (Maybe in a future version – hint, hint.)
Use Case 2: How many databases do I need to look through?
George is new to the company and he needs to get to know how orders are stored in the database. He decides to use SQL Search to look at all the tables that have Order in their name. He is a bit shocked when he find out that there are hundreds of databases on the server and he is not sure which ones he should start with. George, luckily got some help from one of his co-workers Sarah. She told him he should start with three specific databases.
In previous versions of SQL Search, you could look through one database or all the databases. This could lead to a very long list of values if you have quite a few databases on your server. This is now a problem of the past. (And there was much rejoicing.) Now you have the option of selecting only the database you are interested. You can even select the system tables which are all listed at the bottom of the pull down window.
Use Case 3: Rolling the dice for a job.
Liz needs to find all the SQL objects that use the SalesOrder table, but she isn’t interested in searching through tables and views. She does, however want to search through all the jobs first, and then she’ll take a look at triggers, stored procedures and functions.
I found this next new feature a huge bonus. Jobs are not something I’m looking through often, but now I have a way to search through them without any trouble. Simply, select Jobs.
I’m also very happy to be able to select ONLY the database objects I want to search through. For me this means I can ignore tables and views. (Yippee!)
Use Case 4: Getting to know your neighbors.
There have been some implicit conversion problems with some of the date fields in the database. Sarah has been tasked to verify that all fields that end in “DTS” are actually datetime data types and not date data types.
Red Gate has added the ability to see table definitions in the results pane. It’s a simple view of the table structure, but it beats having to go back to the Object Explorer to see the entire table structure. Now when you’re researching field data types, you can do it quickly and efficiently.
Bonus Tips
Here are a few bonus tips that make SQL Search even more useful:
1. You can highlight all the rows in the grid, copy it, and paste it directly into Excel for further reference. This can be very helpful if you are changing all the objects in the list and you want to keep track of the original list. (This isn’t a new feature, but it is an awesome one to know about.)
2. They have improved the indexing of the database objects. This is GREAT news. In the past, if you were expecting a modified object to appear on the list and it didn’t, you would have to close SSMS and restart it in order for the SQL objects to be properly re-indexed. You no longer need to do that. Just go to the Index menu item, and select Reindex.
3. Each of the columns in the grid can be sorted. This can be very handy if you want to deal with all the tables at one time, or a particular schema first.
Don’t Stop Yet
If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:
- Chris Yates: RedGate SQL Search Has What????
- Julie Koesmarno: SQL Server 2012 SSIS Project Versions
- Jeffrey Verheul: SQL Search: The indispensable tool just got better
- To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.
See you next time!!
On a SQL Collaboration Quest
Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.
Original Post: On a SQL Collaboration Quest
I’ve been a fan of the Redgate tools since SQL in the City Charlotte last year. After that we purchased the entire suite for our group and haven’t looked back. Thanks for the reminder to check for a new version as when I looked I was still using sql server 1.1, this is version 2 which I promptly installed.
hi Bill,
You are very welcome. SQL Prompt and SQL Data Generator also have some great new features. I’m itching for some time to blog about both products. I spent yesterday in SQL Data Generator and found all sorts of new features and ways to create test data the way I want it.
Have fun discovering all the cool new features. 🙂
Mickey
Data Generator didn’t really impress me that much, it seemed somewhat limited but we deal with some obscure types of data here so that is probably why that is the case. Our primary reason for purchase revolved around source control. While we still don’t have the process perfected we are light years ahead of where we were in terms of deployments. SQL Compare and SQL Data Compare are top notch tools! I also use a couple of the Embarcadero Tools including Data Architect (for modeling) and their performance profiler which is really nice.
You might want to give SQL Data Generator another run. They have added some new features. The ability to write Python code to help generate data has really expanded the possibilities. Also, Red Gate is great about hearing ideas. If you have some special cases for data types, you can let them know and they can consider it for a future build.
Do you use SQL Prompt too? It’s a must have for me. I’m pretty addicted. 🙂
Like you, I also love Embarcadero’s ER/Studio Data Architect. Take a look at my December posts. The ones for the Advent Calendar series. I wrote several posts on macros that you can use in ER/Studio Data Architect. There will be more posts later this year too. 🙂
Cheers!
Mickey
This is really cool Mickey, as I just blogged on some free tools for SQL Server and SQLSearch was one of them. Nice post.
Thanks Jim.
-Mickey