Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.

It gets better
As you might have read in my earlier blog post, I love using Red Gate’s SQL Search. It saves me a lot of time querying system objects, or looking through source files stored on disk, when looking for a specific query or object. Even though it had a few shortcomings, the tool was really good. And now they’ve solved some of those shortcomings in a new release: SQL Search 2.0. In this short post, I want to quickly show you the changes that make it even more useful for me to use SQL Search.

Search on specific databases
In the previous version you could only search on 1 specific database, or on all databases:

In the new version, it’s more flexible. You can search on all databases or a selected few databases:

This means you’ll be more flexible when searching for specific queries and objects. For example, if you have 2 databases that use an object from a third database, you don’t need to search the whole instance, or execute the search twice on different databases. This can save you a lot of time and effort.

Search on specific objects
Searching for specific objects was the same as the database search box. In the old version, you could only search for all object types, or on one specific type:

In the new release, you can search on any combination of objects you can think of, or search on all objects:

Search results extended
In the new search results, you’ll also see that tables and functions are included. Before you couldn’t see the table definition in the search results, only the table name. Now, the results include the columns of the table. As an example, I’ve searched for tables containing “Employee” on the AdventureWorks2012 example database:

Wildcards
You can also use wildcards and boolean expressions in the search in this new version. It does seem to work on some occasions, but I’ve also seen some weird results when testing this new functionality. But as an example, let’s try out a boolean expression:

And looking at the results, it shows only objects that contain “Employee”, and not “Employees”:

Performance
Because I work with SQL Search a lot, I’ve also noticed that the performance and stability improved a lot. In the old version, I sometimes notices that the searches took a long time, especially searches with a lot of results on instances with a lot of objects and databases. This performance looks better now. But the fact that you can search on specific objects and databases also helps.

You don’t use it yet? Try it out!
Looking at my daily job, I’m almost certain that I can’t live without SQL Search anymore. It makes it easier to quickly find a reference to an object, or views en stored procedures that depend on specific objects. So if you don’t use it now, just try it out. It’s completely free for download at Red Gate.

If you want to read more about this topic, don’t forget to check out these blog posts:

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.

Red Gate? No thank you!
One of the biggest prejudices of the tools from Red Gate is: you have to sell one of your kidneys, in order to afford one of their tools. I agree with you, some of the tools Red Gate sells are pretty expensive. Especially when you need to buy them yourself. But what if I tell you they pay for themselves in the long run? You don’t believe me? Okay, let’s start of with a free tool to convince you.

How SQL Search can save your bacon
As a DBA and SQL Server developer, one of your biggest challenges is to memorize your whole environment, and learn every line of T-SQL there is to find from the top of your head. I’m sorry? Oh, you don’t do that? Well, that doesn’t really come as a surprise. And if you thought: “Hey! I’m doing that too!”, stop wasting your time! No DBA, wherever you will look in the world, will EVER remember all of the T-SQL script, stored procedures, views, etc, that can be found in his or her environment. But where do they get their information from? What would you say if I told you you could search though all objects in a specific database, or on a specific instance?

So how do you do that? You’re planning on making your own script to search through the system view sys.columns for column names, and sys.procedures for Stored Procedure text? There is an easy way out, you know!

Installing and testing
In order to create a test case that you can repeat on your own SQL Server, I’m using the AdventureWorks2012 database. That’s a free example database that you can download from CodePlex. I’ve chosen to download the backup file, that I restored on my local SQL Server.

The installation of SQL Search can be found on the Red Gate SQL site. When you installed SQL Search, a button will be added to the SQL Server Management Studio (SSMS):

So, what do you actually get? If you click the button in SSMS, you’ll see a new window, that’ll act as a result pane. The top of the new screen looks like this:

This screen contains all possible options you will need to find objects either on your server, or in a specific database. You see a textbox on the left, where you fill in your search term. The next few options can be used as a filter. You can search on an exact match (checkbox), search on a specific object only (dropdown list), and on a specific database (dropdown list). The last dropdown list is the instance you want to search on. This list is populated with the open instance connections from your object explorer.

Test case
So how does this work in practice? One of your colleagues comes up to you, and asks you what objects in your database are related to department data. You could search for documentation in order to answer that question, or you could let SQL Search give you the answer. So, let’s search the AdventureWorks2012 for objects that are related to, or contain department data. The result will look like this:

As you will see there are 18 objects that are related to your search term (the count of object is visible at the bottom of the search results on the right). Some objects are shown multiple times, because there are multiple matches on that object. For example the “vEmployeeDepartment” view. The name of the view contains our search term, one of the columns is called department, and the text of the view (create script) contains your search string.

But how does this work with real life situations? How many times do you get the question from your colleagues how many objects are related to a specific table or column? As a DBA you probably get this question more than you would like. Your developers want to rebuild an application, or add a new feature to it, but they’re not sure if the database change they’ll make will break another applications or processes.

It’s also possible for you to use the tool to your own advantage. For example, when you want to know which object can update a specific employee record. You just search for both terms, and you’ll see that there are 3 stored procedures that update employee information:

Please hold on, while I search for the object…
…is a sentence you never have to use again when working with SQL Search. Whenever you found the object you were looking for, you can just double click on it, or use the button between the result pane, and the script pane:

This will look up the object in the object explorer. So you never have to look for an object after you found it. Just let SQL Search do all the hard work.

Red Gate? They’re AWESOME!!!
Hopefully you changed your mind about Red Gate tools, after reading this article. This is one of the tools I personally use on a daily basis. Even though there is documentation within your company, you still need to find it. It’s printed and laying around somewhere, or on an intranet or SharePoint. You know where you can find it, except when you REALLY need it!

SQL Search is also a tool you (in my opinion) really need when you’re new to a company. You’ll see a lot of different databases, with different purposes, and maybe even different DBA’s responsible for that specific database. Using SQL Search gives you a great advantage when you need to have a chat with the DBA. You’ll step into their office with a little more knowledge of the database, without reading endless documents, cryptic ERD’s and folders full of unnecessary documentation.

Feedback
When you start using the tool, don’t forget to thank the people from Red Gate. They LOVE to hear your feedback, either in a tweet, the Red Gate Forums, or by contacting support. You could also send me a mail or tweet, or leave a comment at the bottom of this post. I would love to answer your questions (as far as I can), or pass them on to Red Gate.

If you want to read more about SQL Search, don’t forget to check out these blog posts:

A few weeks ago I received the official confirmation that I’ll be a part of the Friends of Red Gate 2014 program. This program allows the participants to directly communicate with the product teams, provide feedback to teams, and try out beta-builds of tools.

The Red Gate tools are easy to use, and give you the upper hand on administering servers and databases. So together with other Friends of Red Gate, we decided to write a series of blog posts about the tools. The first one will be posted tomorrow. In these blog posts, we will try to show you why you should use their tools, and what problems they can solve for you.

The group that will write posts just like me consists of 3 other people: