ssms

Script Multiple Objects [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever noticed that you can only highlight 1 object in the Object Explorer in SQL Server Management Studio? This usually is enough for most things you do, but if you want to script multiple objects at once… it becomes a pain. Well there is a way to script multiple objects at once, and it’s super easy.

  1. Open the Object Explorer Details screen by selecting View -> Object Explorer Details.
    Script Multiple Objects 1
  2. Highlight the folder with the objects that you would like script. A list of all objects in that folder will appear on the right. This works for tables, stored procedures, etc…
    Script Multiple Objects 2
  3. Highlight all the objects that you want, right click, and select Create To.
    Script Multiple Objects 3

Adding All Columns In A Table To A Query [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

I wanted to share this quick tip with you on how to add a list of all the columns in a table to your query window in SQL Server Management Studio. This is the easiest way I know!

  1. Expand the Object Explorer to show the table that you want to add the columns for.
        Adding All Columns 1
  2. Select the Columns folder on to your query window.
        Adding All Columns 2
  3. Drag the whole folder on to your query window and just drop it where you want it. That’s it!
    Adding All Columns 3

Compressing Data In SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

In SQL Server 2016 they added a sweet new featured to allow you to compress input strings and binaries. The new COMPRESS function allows the field or literal value passed in to the function to be compressed in GZip format.

 
COMPRESS
The COMPRESS function will take in an input and compress it using a GZip compression. The output from the function is a VARBINARY datatype. You can display that in a SQL Server Management window or store it in a table with a VARBINARY(MAX) field. To use the COMPRESS function, you need to pass in either VARCHAR, BINARY, VARBINARY, or CHAR datatypes.

 
How To Use The COMPRESS Function
For this example, I’m going to put the data in to a table variable. If you have a permanent table structure, you can use that instead.

If you’re looking to compress some data just inside SQL Server Management Studio, you can just do this:

 
 
How To Decompress Your Data

So now that you’ve compressed data, how to do you decompress it? As you guessed, SQL Server also added a DECOMPRESS function. It’s just as simple as the COMPRESS function, just with one little twist. The DECOMPRESS function only returns the datatype VARBINARY(MAX). You will have to cast it in to whatever you want. Here’s an example using the same script as we used above:

You can see above that I just wrapped the DECOMPRESS function with a CAST function. It’s that simple to get it in the format that you want.

 
Something To Keep In Mind: Compressed data can’t be indexed! Sorry!

Something Else To Keep In Mind: If the goal is to compress all the data in a row/table/data page/or index, SQL Server (as of 2016) now supports this using a different built-in method. So you don’t have to do it all manually.

SQL Server Management Studio Dark Theme

by
Facebooktwitterredditpinterestlinkedinmail

If you like the dark theme of Visual Studio and wish that you had the dark theme for SQL Server Management Studio (SSMS), look no further.  Management Studio actually comes with a dark theme, but it just isn’t enabled.  It’s super simple to enable this feature.  Here’s how.

Note: I’ve confirmed this works with SQL Server 2016.

Here is what my current themed Management Studio looks like:
SQL Server Management Studio Dark Theme Light

 

The place in SQL Server Management Studio that we set the color theme is in the Options.  To see what we currently have, you go to the options by clicking Tools –> Options…
SQL Server Management Studio Dark Theme Options

From there we navigate to Environment –> General.  On the right side we can see Color theme:.  This option shows the current color theme that you are SSMS is using.
SQL Server Management Studio Dark Theme Options

My only options in the drop-down are Blue and Light:
SQL Server Management Studio Dark Theme Options

To allow the Dark theme to be selected, all we need to do is make a small change to the ssms.pkgundef file.  This file is located at: “C:\program files (x86)\Microsoft SQL Server\130\tools\binn\managementstudio\ssms.pkgundef”.

 

Step 1) Close SQL Server Management Studio and then browse to the ssms.pkgundef file and open it with Notepad.  The easiest way I found is to right click on the file and choose Edit.

TIP: If you can’t save the file after editing it, you may need to launch Notepad as administrator.

SQL Server Management Studio Dark Theme Edit

Step 2) Find the // Remove Dark theme key.  The easiest way is to just do a search in Notepad.
SQL Server Management Studio Dark Theme Edit

Step 3) Add 2 slashes (//) in front of this key that starts with [$RootKey$\Themes\.  Save and close the file.
SQL Server Management Studio Dark Theme Edit

Step 4) Relaunch SQL Server Management Studio.  Go back in to the options and select the Dark theme.
SQL Server Management Studio Dark Theme Done

This is what your final product should look like!  Enjoy!
SQL Server Management Studio Dark Theme Dark

 

SQL Server Management Studio Keyboard Shortcuts

by
Facebooktwitterredditpinterestlinkedinmail

Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.

Ctrl + R – Show/Hide the results pane

Ctrl + N – New query window

Ctrl + Shift + U – Switch current selection to uppercase

Ctrl + Shift + L – Switch current selection to lowercase

F5 – Execute the currently selected query (or entire query if nothing is selected)

Ctrl + Tab – Switch to the next open tab

Ctrl + Shift + Tab – Switch to the previous open tab

Ctrl + Space – Pops up IntelliSense complete if it is not already open

Ctrl + Shift + R – Refresh local IntelliSense cache

F8 – Open the Object Explorer if not already open

 

 What other SSMS keyboard shortcuts do you use?