Tag Archive for Collaboration

On a SQL Quest Using SQL Search by Red Gate

OLYMPUS DIGITAL CAMERA

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 

The First Mission

For our first collaborative entry in this blog series, we have chosen to investigate Red Gate’s FREE tool, SQL Search. The question we posed to each other was: What is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

The Search Begins

Right now I’m in retro-fit mode at work. I have an application that I recently inherited that needs a new feature. This requires me to add new columns to tables in the database(s), as well as find all the objects (Stored procedures, triggers, etc.) that reference the tables I need to modify to make sure they will still work and to add logic to support the new feature.

Red Gate has a whole suite of tools for me to use on my current adventure at work, but the tool I used the most this past week was their FREE tool, SQL Search. This tool will work with SQL 2005 and up.

Instead of writing about how to use SQL Search, I want to highlight some shortcut keystrokes that will improve the speed of using this awesome tool. Did I mention it is a FREE awesome tool?

The Task

I have a set of tables that I modified and I need to look at the code for all the objects that reference that set of tables to not only make sure they still worked properly, but to add code to support the new feature that was added. I’ll use the Adventureworks2008R2 database in my example below.

After I found the list of objects I needed to inspect, I did the following:

  1. Scripted out the object as an ALTER statement.
  2. Quickly searched for the table in the script (some of the scripts were 1000+ rows long).

Here is my favorite part

If you double-click one of the objects in the SQL Search list of found objects, that object will be found and selected in the in the Object Explorer. Unfortunately, if that object is a view, it takes several mouse clicks to get an ALTER script created, soooo

With the help of Mark Holmes (b|t), we figured out what the hot key combination is to script out the highlighted object as an ALTER statement. You ready? SHIFT  F10 + S+A+N. Yes, that is a lot of keys, but if you are like me, keystrokes are faster than mouse clicks.

Note: I remember it as SHIFT F10 + SAN

The CTRL + F shortcut will take you into the Find dialog box where you can search for the name within the script. If you had been searching  for the same value prior to this script, then F3 is your best friend and it will take you directly to the search term in the ALTER script.

But wait! There’s more

If you are a Red Gate junkie like myself and have SQL Prompt installed, then there is another shortcut you can add between SHIFT + F10 + SAN and F3.  If you use CTRL K + CTRL Y then you can have the alter script formatted to your taste. (aka, you can read it faster. Win!)

Note: I remember it as CTRL + KY

image

Scenario Time

So here is what it would look like if you were using the Adventureworks2008R2 database and looking for the Person table.

  1. Enter Person into the search field
  2. Select the “Exact Match” option. (Optional)
  3. Select the “All Objects” from the list of objects
  4. Select “All Databases” from the list of databases (The search term is automatically searched. Hitting ENTER is not required.)
  5. Click on one of the objects listed
  6. SHIFT  F10 + S + A + N to create an ALTER script
  7. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  8. CTRL  F to pull up the SSMS search window to search within the script
  9. Type in Person and click enter. You’ll be taken to the first instance of Person
  10. Use F3 to find the other instances of Person in the script

Now to look at a second script. You already have the list of objects, so you don’t have to search for them again.

  1. Click on one of the objects listed.
  2. SHIFT  F10 + S + A + N to create an ALTER script
  3. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  4. Use F3 to find the other instances of Person in the script

Repeat these four steps for each script you need to look at. Win!

Don’t Stop Yet

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

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

On a SQL Collaboration Quest

Announcer: Twelve SQL Professionals started on a quest.

Director: No, no. They were FOUR SQL Professionals.

Announcer: Four Professionals started on a quest to find the sequel to the Holy Grail.

Director: No, no. They were SQL Professionals and they were on a quest for knowledge.

Announcer: Oh, right, right.
Four SQL Professionals started on a quest for knowledge and not for the Holy Grail. All though it could be said, that knowledge is the ultimate Holy Grail and it is always sought by the wise. The four SQL Professionals, Mickey Stuewe, Chris YatesJeffrey Verheul, and Julie Koesmarno (MCJJ for short) gathered from the four corners of the world: California – USA, Kentucky  – USA, Rotterdam – The Netherlands, and Canberra – Australia to meet at a local Skype tavern to determine the direction they would take on their quest for knowledge, and not for the Holy Grail.

After much eating and drinking, they rejoiced (Yeah!!!!). They had found the path for salvation.

Director: No, no. They found the path for their quest of knowledge.

Announcer: Right, right.
After much more eating of chocolate and drinking of whiskey, they rejoiced (Yeeeaaah!!!). They had found their path for knowledge. They decided they would pose questions to each other and respond as a group through their blogs at  mickeystuewe.com, chrisyatessql.wordpress.comdevjef.wordpress.com, and mssqlgirl.com. They decided they would also ask other SQL professionals they encountered along their way on their quest.

So now here, their quest begins at the Bridge of Death… (I hope they know the answers to the questions, or this collaborative series will be very short.)

BridgeBridgekeeper: Stop. Who would cross the Bridge of Death must answer me these questions three, ere the other side he or she see.

Lady Mickey: Ask me the questions, bridgekeeper. I am not afraid.

Bridgekeeper: What… is your quest?

Lady Mickey: To seek knowledge about the SQL world and not to seek the Holy Grail.

Bridgekeeper: What… is your favorite color?

Lady Mickey: Red.

Bridgekeeper: What… is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

Lady Mickey: Funny you should ask that. I have this post that I’m publishing tomorrow. In fact, we each have a unique answer to that question. ButYou’ll have to wait until 2pm GMT tomorrow.

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.

%d bloggers like this: