Headache + Pain <> Red Gates SQL Search

Recently, I started diving into my utility belt. There are tools that I’ve had at my disposal for some time, some that I have tried and not gone back to, and ones that I use everyday. This has a definite potential to start exploring these utilities over the next couple of months and one that I touched on last few weeks that I want to expound a bit more on today is Red Gates SQL Search utility.

I’ve come accustomed to this utility as being my quick hit Sherlock Holmes style of investigating all sorts of issues that a common data professional may encounter throughout the work day.

The Utility

SQL Search is a “free” tool that is an SSMS add-in that pretty much allows you to scan across all databases for a plethora of information for all objects. How do you access this utility?

Once downloaded and installed within SSMS you will find it on your tool bar as indicated below:

SQLSearch1

By clicking on the SQL Search icon or by utilizing the hot key of (CTL+ALT+D) you will open the SQL Search Tab in your current listed session below:

SQLSearch2

The Search Begins

Once you have this open I’d like to point out a few items.

  1. In the last image shown you will notice that in my Object Explorer I have my (local) instance selected and highlighted. If I had multiple instances in my Object Explorer SQL Search would open the tab on whichever instance I had highlighted.
  2. I can now begin my search. I have multiple options at my disposal on what I can check for and I can set limitations or filters if you will by selecting what type of object I’d like to filter on as noted below:
  3. The last two options are self explanatory with what database you are searching in, but I do want to point out the last option where I have (local) in my drop down. Currently, I do not have a second instance fired up in my Object Explorer; if I did then you could click this drop down and hop over to the other instance. This feature is offers nice compatibility when you have to work with multiple instances at once time.

SQLSearch3

By Jove Watson We’ve Found It

Now here is where the magic begins. For example, I know that I have a table called Release but I cannot remember where I saw it at, or maybe you’ve been in a meeting before and you leave with schema names but you’ve never seen the lay of the land before. Well here you go; if I type in the term Release and set it for all objects, scanning all databases my results are below:

SQLSearch4

…but it gets better. If you look at the link that says “Select object in Object Explorer” it will take you directly to the location inside the DB where the object is located for review

SQLSearch5

Fancy but now what?

SQL’s version of Scotland Yard says this is all well and good but can it do more? Let’s step back and think about this a moment. The whole goal, with any business I hope, is to make processes and procedures more efficient. This utility makes a strong case for the data professional to add this to their arsenal. Here are some real examples on what I utilize this tool for that haven’t been mentioned thus far:

  • Off and on code reviews are done. Have you ever ran across the infamous /* TO DO */ comment? Heck I’ve put those comments in before in my own sp’s noting I need to finish something at a later time. I can quickly scan a DB and easily find these gaps in seconds
  • Select *; don’t fool yourself. While these are frowned upon they are still used. Utilizing this tool has helped me pin point such code in seconds which then proceeds my conversation with the author of the code or update
  • I’ve utilized this utility as my central hub for branching out when I know parts of names to objects. Gives me quick insights into the schematics of the database and what dependencies objects have prior to making any changes
  • Efficiency – this one word carries so much weight. I wish I had this utility when I was first starting out; for me this product is definitely a game changer and did I mention it is free? Why not take advantage of it > go check it out now you know you need it in your data professional tool belt

Are You Using SQL Search Already?

If you already are utilizing this utility then drop Red Gate a line and let them know what your thoughts are on the subject; Red Gate always enjoys getting feedback and I have never seen a more thorough company in taking use cases and every day occurances by the user and figuring out how they can make their products better.

Contact Red Gate

More utilities from Red Gate

If you like what you’ve seen thus far; than you haven’t seen anything yet. This will become a mini series I’m proud to work on with some other individuals that are part of the Red Gate family. To see their twist please check out:

Julie – SQL Tools Review: SQL Search
Mickey – On a SQL Quest Using SQL Search by Red Gate
Jeffrey – How SQL Search saves you time

Entering the SQL Matrix

MatrixI know why you are here and I know what you’ve been doing. You hardly sleep and why night after night you sit by the computer; you are looking for what they call ~ The SQL Knowledge. I know because I too have looked for the knowledge, and when that knowledge found me it told me I was not really looking for it; I was simply looking for an answer. It’s the question that drives SQL Data Professionals and is the question that brought you here……

The SQL Matrix is comprised of many components and with that comes SQL Data Professionals from all walks of life from all over the world. It has been amazing to see components of peoples skill set come together to provide different view points on various topics.

Well, I’m about to welcome everyone to the Desert of the Real where real SQL Data Professionals collaborate with one another on various topics related to SQL. What you will come to know as MCJJ (Mickey Stuewe, Chris Yates, Jeffrey Verheul, and Julie Koesmarno); you will find four distinct Data Professionals who seek to gain more knowledge inside the SQL Matrix and provide thoughts and real life scenarios into series of blog posts.

With that said I now must tell you; you have two choices:

  1. You can take the blue pill and go back to sitting in front of your computer all day long stuck in the mundane rut that happens to all of us
  2. You can take the red pill and join MCJJ on this collaboration effort and perhaps hopefully pick up some new and interesting SQL Tips along the way.

The option is yours what will you decide.

Tomorrow will mark our first collaboration; I’m happy to announce that it will be done on Red Gate’s Utility – SQL Search.

Check out my peers reviews tomorrow and also check out the others collaboration release posts.

Stay Tuned

Please visit the following links to see the unique views of my collaborators.

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

Renovations On The Way

ProfessorWhat Is The SQL Corner

I attended my first PASS Summit back in 2011 in Seattle and quickly realized that I could be doing more for the community that what I was currently doing. When I came back I thought I could tackle the SQL world, leap tall buildings in a single bound, round up all the unnecessary bad practices and so on. The SQL Corner came about as a meeting place where people could come and gather to find solutions, present new techniques, and share some knowledge.

Growth

It has been an enjoyable three years in continuing to write for this blog and other avenues. I have realized that over time I enjoy being able to discuss what I may be going through and solutions found to issues that occur in an DBA’s life.

As time has progressed the community has seemed to welcome what The SQL Corner represented “Leadership Through Service”.

Time For A Change

With time, things change. In the latter part of last year I’ve developed a passion for writing more, sharing more in the community, and speaking to various groups. I started to see morphing The SQL Corner into something more; transitioning it over to what will become the SQL Professor.

Why The Change

The name change represents where I am at and what I’d like for the SQL Corner to become; a place for “continued learning”. In my 12+ SQL career one thing has always remained the same – there is always something to learn and always a skill set to work on. For me, I believe this is why I enjoy working with SQL so much. I enjoy coming into work everyday and the job of being a DBA. With being a DBA comes great responsibility and striving to continual learn new things is a passion that I hope will never burn out.

Upcoming

With all that said you will start to see some renovations being introduced over the next few months both from this blog standpoint, social media, etc. I’m thankful for the ride I’ve been on over the last few years and looking forward to what the new journey will hold.

Feedback is welcome

T-SQL Tuesday #051: Place Your Bets

SQL-Tuesday.jpgThis months T-SQL Tuesday block party is coming from the renowned Jason Brimhall (blog|twitter). You see each month a SQL Community member hosts this block party and this months theme is “Place Your Bets”.

So you want to gamble? Come on up to the SQL table and place your bets. Any Data Professional is welcome – junior level up to senior level all money is acceptable at the SQL table.

Okay, I’m in what are we betting on today. Well, you are in luck my friend today’s bet is on backups; sounds simple enough doesn’t it? Sure that sounds like fun I’m all in, well wait what about backups?

You’re lucky you asked, otherwise you’d be called a sucker and I’d just would have taken the all in to the house “Little Joe”.

The Scenario

It was a dark grey morning…oh wait that’s a different story. Let’s say you have a plethora of databases that are all business critical and you have automated tasks that backup these databases. If something happens while in the backup process and the process fails than a failure notification is sent out notifying the advantageous Data Professional that their process had failed and go take a look so you can fix it. All is well, right?  Most would say yes, some would say no, and then there is some, the gambler, who says who the heck cares. You have the backup process in place ~ Let’s Roll.

The Gamble

I bet on that scenario early on in my career. I went all in with the house and you know what, that didn’t pan out to well for me. Why you ask, well gambling on whether or not my backups were solid and good opened my eyes to something that I knew but didn’t really take into consideration in the beginning stages of my career. I had a critical database being backed up…phone rings. The proud DBA picks the phone up…yes we have an issue and we need to look at our backup for x date. Sure thing, I got it right here. I’ll restore it and we’ll take a look at it.

Go to restore and the backup is corrupt; initially I’m thinking well that isn’t good. It was then when I had to go back and tell the business that the backup was corrupt and I would need to go a day before or after to get the information – but wait Mr. Gambler what about T-Logs did you have those – um nope business deemed it not necessary and didn’t want to pay for space etc for the growth needed.

Conclusion

Even after taken precautions in my backups I still feel the strong need to ensure testing of the backups is being done whether it is through an automated process, spot checking, etc. Taking a backup is great, can that backup be restored? Are the settings on the backup set properly? If you can’t answer some or all of these then take time today to do some checking.

Each shop is different and will have it’s hurdles to climb. With that said are you all in? Do you want to take that gamble and bet against house? Business looks at backups as a safety net or in some cases really doesn’t care as long as you have them. To the Data Professional they are much more.

I’ve always been taught to work hard and hone your skill set; for me backups fall right into that line of thinking. Always keep improving, learn from your mistakes. From mistakes comes growth and don’t be afraid to fail. Many successes have come from failures or setbacks.

What is T-SQL Tuesday

T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

SQL Prompt What Is It Good For Absolutely…

SOSEverything. If you’ve never had the opportunity to use Red Gates SQL prompt utility then you are missing out. Recently, I went without it for a day by some circumstances that I decided to create for myself. Of course these actions were not intentional but then it got me thinking somewhat on if I was stranded on a desert SQL island what would be the one utility I would want most in my survivor tool belt.

I posed this question over at SQL Brit’s Forum (if you haven’t been over there yet check it out; there is some good stuff going on over there) and got some really good responses back.

Back to the subject at hand, SQL Prompt, and why do I like it so much. I am a heavy Red Gate user; have their bundle and enjoy using all their utilities. Makes my job a lot easier; one of these is SQL Prompt.

What Is SQL Prompt?

SQL Prompt is an add-in for SSMS and Visual Studio, and according to Red Gate, has been designed to strip away the repetition of coding.

Features

There are many features about SQL Prompt but I won’t cover all of them. I want to focus on just a few that have helped me tremendously fight the good DBA fight.

Summarize Script Option – as you can see below the script that is in the query window for my test database called release I have 3 insert statements. If I had a very huge query I was working with, or perhaps even going through highlights of an SP I can get a quick summary of what I’m dealing with:

RedGate1

If you click on one of the Insert Scripts you will see that it is highlighted in your query window for quick review:

RedGate2

Just how do you get to this Summarize Script Option?  If you look at your toolbar inside SSMS you will see the SQLPrompt option available. From there it is as simple as selecting the summarize script feature:

RedGate3

Snippets – these are nice hot keys to allow you to get to code quicker. You can save your own snippets for frequently used code. For example some of the snippets that are quick to learn are

  • SSF which is select * from
  • DF which is delete from
  • CT which is create table
  • II which is insert into

RedGate4

The list and possibilities could go on but the mere fact of having this at my finger tips has cut down on a lot of coding and “repetition” that occurs on a daily basis.

Conclusion

What I’ve shared are just two features that SQL Prompt has to offer and we haven’t even begun to scratch the service. The more I think on it the more I believe I will be doing an in depth over view of my bundle. I believe I have features that I am not taking full advantage of that are at my disposal.

Just this utility alone has formatting short cuts, navigation short cuts (Summarize Script is Ctrl+B, Ctrl+S), Code-completion Shortcuts, Refactoring, and much more.

Check out SQLPrompt for more information at RedGates website.

What’s your go to utility? If you were stranded on a SQL Desert island what would you want to have with you that you could not survive without?