In my last article, I went over some of the commonly expressed “annoyances” in SQL Server Management Studio, as well as infrequently known methods for alleviating each of these annoyances. In this article, we will voyage deeper into the bountiful reaches of SSMS, and explore a handful of other features which you may or may not know about.
If you’ve never run into this before, you’re in for a treat. Traditional search (Edit > Find and Replace > Quick Find, or Ctrl + F) is very feature-rich in SQL Server. It allows you to specify case options, search direction, and even to use regular expressions and wildcard characters. For quick searches though, it’s rather clunky. It pops up a window overtop of the query text that does a really fabulous job of completely obscuring the text that you’re looking for. It’s also somewhat unforgiving with respect to exact matches - you’ll often find yourself searching for something like “Employees” and coming up with zero results, when really you’re looking for “Employee”.
Enter Incremental Search. Go ahead and open up SSMS, and script out one of your larger procs. Click on the query pane, and press Ctrl + I. Your cursor will turn into a little downward arrow with a set of binoculars beside it. Now start typing a word to search for. After each keypress, SSMS will jump to matching words. Hitting F3 will then cycle through the matches onscreen, regardless of their direction with respect to your cursor. Ahhhhh…..that’s much better.
Find in Files and Replace in Files
Ever scripted out a bunch of objects from a dev database, and gone to deploy them in a production db, just to find out that you scripted out the “USE DevDB” statements as well? You’re then stuck opening each .sql file and editing out the USE statements, or using a third-party utility like TextPad to search through the files. Not so when you use “Replace in Files” (Edit > Find and Replace > Replace In Files). Simply specify the string to find, the string to replace it with, and the area to search. You can search SSMS solutions, all open documents, the current document only, or even select a set of folders to search within.
Block Selection in the Query Window
Many a time have I meticulously written a query, complete with proper casing and perfect indentation, just to get to the last line (usually a GROUP BY) and find out that I didn’t leave enough space between the first and second columns – something like this:
SELECT sod.ProductID, SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail sod
WHERE SpecialOfferID = 13
GROUP BY sod.ProductID -- Curse you GROUP BY!!!!
It’s not so bad in this case, because there are only three lines to “fix”. A real pain in the neck when you have ten or fifteen lines in your SELECT and JOIN statements, if you have to individually adjust the spacing for each line. Block comments save you the trouble of adjusting each line individually. Simply press and hold “alt”, and then using the mouse, select the first few characters from each of the lines that you want to bump. It’ll look something like this:
Fetching Column Lists Using Object Explorer
Best practices dictate that you use “SELECT *” as infrequently as possible (with a few exceptions). It’s a real pain in the butt to type out all of those column names though, isn’t it? You have two options here. One, which most of you are probably already aware of, is to script out a SELECT to your clipboard. To do this, right-click on the table, and select “Script Table As > Select To > Clipboard. You’ll get something like this:
Many people don’t use the “one column per row” format, and standards are important, so for those of you who don’t, you can get a listing of all of the columns in the table in a single row by expanding the table in Object Explorer, clicking on “Columns”, and dragging the folder into the query pane. You’ll then need to break that “single row” into an appropriate number of reasonably sized rows. JFYI – this also works for a number of other Object Explorer nodes, such as triggers, indexes, and the like.
That’s all for now – I’ll cover more SSMS tips and tricks in my next article. Before I “go”, let me draw your attention to the Community menu at the top of SSMS. Without even leaving SSMS, you can Ask a Question on the MDSN Forums, check on the status of any threads that you’ve opened up, or even submit SQL Server Feedback on Connect. There are a number of other options on the Community menu that I haven’t gone into, so be sure to check each one out!