Happy New Year! My New Year's resolution for 2018 is to help you become a better SQL developer.

I want to start off with that today by showing you a much better way to generate database object change scripts.

Watch this week's video on YouTube

If you are like I used to be for YEARS, anytime you want to copy a table, index, etc... you probably right click on that object in SQL Server Management Studio and click "Script as" > "CREATE To":

An inefficient way of generating change scripts

This is a pretty easy way to quickly script database objects, however it's incomplete.

For starters, I'm forced to generate the scripts for tables one at a time.  Not fun.

Additionally, if I want to script associated objects for that table, like indexes, I have to go to each index and then right-click and select "Script Index as".  Ugh.

There Is A Better Way

Instead of using "Script Table as", you can right click on your database and choose "Tasks" > "Generate Scripts...":

This option brings up a GUI that will allow you to script multiple objects at the same time:

Look ma, multiple objects at once!

Additionally if you click the "Advanced" button in the final page of the dialog, you will receive many more options for how your objects will get scripted, including the ability to script out the indexes!

Ooooooo, ahhhh - everything scripted in one fell swoop!

Using the Generate Scripts Task it's easy to generate table, index, stored procedure, etc... change scripts all in one step - nice!

NOTE: If your SQL User receives an error when trying to generate the scripts, make sure they have the following access:

USE [MyDatabase]
GO

GRANT VIEW DEFINITION TO [MyUser]
GO

GRANT SELECT ON sys.sql_expression_dependencies TO [MyUser]
GO

If there's one keyboard shortcut I use more than any other (with the exception of copy and paste) it would be the ALT + highlight multi-line edit/block selection shortcut.

Let's take a look at three ways the ALT + highlight shortcut lets you work faster in SQL Server Management Studio.

Watch this week's video on YouTube

1. Add results of one query into the IN() statement

Sometimes when writing an ad hoc query  you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement...but that's too much work for a one-time use disposable query.

What you can do instead is:

1. Copy your values of interest
2. Paste them into your IN() statement
3. Hold down the ALT key while dragging the mouse down in front of all of your pasted values
4. Type a comma (see video above for an easier demonstration).

This allows you to quickly turn pasted values into a list suitable for an IN() statement.

2. Paste values into a template of commands

Sometimes you might have to do something repetitive, like insert a bunch of values into a table.  You could script out multiple value insertion with a UNION ALL and and INSERT INTO statement, but once again that's a lot of work for a one-time query.

Instead you can copy the values you want to insert by highlighting them while holding down the ALT key then positioning the cursor on the first location where you want to paste the values.  Then once you paste, SSMS will automatically multiline paste the values.

Once again, watch the video above to see a demo - a picture (video!) is worth a thousand words.

3. Quickly modify multiple rows

Finally, you might have ran into a situation where you already have a set of queries written, but they all need to be slightly modified in order to work.  For example, maybe the table name changed and now you need to append a prefix to each table name.

While holding down the ALT key, you can simply drag in front of the text you want to prepend to and then start typing.

Voila! You just edited multiple lines in one go.

Every once in a while I discover a SQL Server Management Studio trick that's apparently been around forever but is completely new to me.

Today I want to point out one of those features that had me thinking "how did I not know about this before":

The F1 keyboard shortcut.

Watch this week's video on YouTube

To use it, highlight a command or function that you want to know more information about and then press F1.  Simple as that.

Pressing F1 brings up the Microsoft online documentation for that keyword/function, making it the fastest way of getting to Microsoft's online documentation.  You'll solve your own questions faster than a coworker can tell you "to google it."

Most recently I've been using the F1 shortcut in the following scenarios:

• Can't remember the date/time style formats when using CONVERT?  Highlight CONVERT and press F1: BOOM! All date and time style codes appear before you.
• Need to use some option for CREATE INDEX and don't remember the syntax?  Just highlight CREATE INDEX and press F1!  Everything you need is there.
• Do you remember if BETWEEN is inclusive or exclusive?  F1 knows.  Just press it.

You get the idea.

Assuming you use the online Microsoft docs 10 times per day, 250 days a year, and each time it takes you 10 seconds to open a browser and search for the doc...

( 10/day * 250/year * 10 sec ) / 60 sec / 60 min = 6.94 hours saved.  Your welcome.

Watch this week's video on YouTube

Here's the scenario: you copy and paste some code into a query you are building. A few minutes later you need that same snippet again, but you've already copied and pasted something else onto the clipboard.

The next five minutes of your life are spent searching across the twenty query editor tabs you have open looking for that original piece of code.

Sound familiar?

THERE'S A BETTER WAY!

Copying and pasting is a feature that's available in nearly every text editor ("nearly" — anyone remember the days before iOS had a clipboard?).

However, SQL Server Management Studio goes above and beyond the regular copy and paste feature set — it has a clipboard ring.

What's a clipboard ring you ask?

The clipboard ring let's you cycle through the last 20 things you copied onto your clipboard when you go to paste in SSMS. It can be accessed in the Edit menu (like in the screenshot above) or by using the keyboard shortcut CTRL + SHIFT + V.

Let's say you have the following queries:

----------------- Query 1 --------------------------
SELECT FruitId FROM dbo.Fruits WHERE Name = 'Apple'
----------------- Query 2 --------------------------
SELECT FruitId FROM dbo.Fruits WHERE Name = 'Banana'
----------------- Query 3 --------------------------
SELECT FruitId FROM dbo.Fruits WHERE Name = 'Orange'

And let's pretend you want to copy all of the fruit names into the IN statement of this query:

SELECT FruitId FROM dbo.Fruit WHERE Name IN ()

Instead of copying and pasting each fruit separately, you can batch your copies together and then paste them from the clipboard ring into your IN statement at the same time:

Use this trick the next time you need to find that snippet of code you used right before heading off to lunch and I guarantee you will be saving yourself tons of time.

SQL in 60 Seconds is a series where I share SQL tips and tricks that you can learn and start using in less than a minute.

Watch this week's video on YouTube

Have you ever copied and pasted a query into SQL Server Management Studio and been annoyed that the list of column names in the SELECT statement were all on one line?

SELECT Col1, Col2, Col3,  Col4, Col5,Col6,Col7, Col8, Col9, Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,Col27,Col28,Col29,Col30 FROM dbo.MyTable

You can make the query easier to read by putting each column name onto its own line.

Simply open the Find and Replace window (CTRL + H) and type in ,(:Wh)* for the Find value and ,nt for the Replace value (in some versions of SSMS you may have better luck using ,(:Wh|t| )* in the Find field). Make sure "Use Regular Expressions" is checked and press Replace All:

The magic you just used is a Regular Expression, and Microsoft has its own flavor used in SSMS and Visual Studio. Basically, we found text that

• began with a comma (,)
• followed by any whitespace (:Wh) (line break, tab, space, etc…)
• (in newer versions of SSMS we add |t| to indicate or tab or space)
• and replaced it with a comma (,) and a new line (n) and tab (t).

Sure, this trick isn't going to give you the same output as if you used a proper SQL formatter, but this technique is free and built straight into SSMS.