SQL & SSMS Tricks

Just posting some tricks I've used or just learned

http://www.e-squillace.com/ssms-tricks-shortcuts/

SSMS Tricks or Options I like

• ALT + SHIFT =multi-line select > cool
• Options > Text Editor > All Languages > Scroll Bars > Use map mode for vertical scroll bar > cool
• Splitting query windows
• Options > Text Editor > All Languages > check Line Numbers
• Dark theme/Font
• https://sqlstudies.com/2018/04/04/ssmss-dark-theme/
• or Dark fonts only https://sqlstudies.com/2015/11/16/ssms-setup/
• Making SSMS Pretty : My Dark Theme - SentryOne Team Blog
• https://github.com/tcartwright/SSMSThemeEditor
• 
  
• Options > keyboard > Keyboard: search command Query.ChangeConnection , press shortcut keys ALT + G, and Assign
• Options > keyboard > Query shortcuts; Set to anything you want. In future, highlight a table name (with or without schema) and CTRL+3. these are my settings
• ALT + F1 = sp_help
• CTRL + F1 = sp_helptext
• CTRL + 1 sp_who
• CTRL + 2 sp_lock
• CTRL + 3 SELECT TOP 100 * FROM 
• CTRL + 4 SELECT COUNT(*) FROM
• CTRL + 5 SELECT * FROM
• CTRL + 6
• CTRL + 7 sp_helpdb
• CTRL + 8 USE <database name>
• CTRL + 9 sp_helptext
• CTRL + 0 sp_whoisactive
• CTRL + SHIFT + R to refresh IntelliSense
• ALT + SHIFT + T to move down 1 line
• CTRL + SHIFT + ENTER to create new empty line below
• Options > Query Execution > Advanced - uncheck SET ARITHABORT
• Options > Query Results - Play the Windows default beep
• Change Environment (Object Explorer font) to go bigger
• Script out permissions in SSMS
• In SSMS (Management Studio) you need to go to Tools-->Options  then look for "SQL Server Object Explorer" and expand it and go to "Scripting".Then look for Object scripting options and change "Script Permissions" to "True".

Starting SSMS with a specific connection and script file - SQLServerCentral

Configuring SSMS for presenting - Paul S. Randal

SSMS Shortcut Keys
Bookmark - CTRL K, K to bookmark, N for Next, P for Previous
ALT+F8 to open Object Explorer
SHIFT+F1 on a keyword/object (or DMV) to open up in browser

SSMS Add-Ons (FREE)

• Red Gate SQL Search
• APEX SQL Complete - free SQL complete
• APEX SQL Refactor - free SQL Formatter
• APEX SQL Search
• Poor SQL http://poorsql.com/  - SQL Formatter

SQL Code tricks

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

--instead of (NOLOCK) on every table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

-- /* First line. Removing the two dashes activates the block comment

SELECT 

patientname,

Patientid,

Language

FROM whatevertable

Where name = 'Whatever'

-- */ Last line. When the block comment is on, this terminates it


Resources
http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/ 

List All Groups, DisplayName, ServerName in CMS (Central Management Server)

If you use CMS
If you have a large group of servers and can't memorize all the names/IPs
Use below code to show them all (and copy to Excel to format)

Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)

WITH MyCTE
AS (
--root, anchor
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
WHERE is_system_object <>1 AND parent_id = 1
UNION ALL
--child
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id
)
SELECT
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END
,G.name, G.ParentName
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'
FROM MyCTE  G
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id
WHERE 1 = 1
ORDER BY
GroupLevel ASC, Parent, Child, [Display name]

SSMS Tools Pack 1.5 (for SQL Management Studio 2005/2008)

Can't believe I missed it, but new version of my favourte "free" SQL Management Studio add-ins is available, SSMS Tools Pack version 1.5

New/improved features include:

• Window Connection Coloring (a colored strip indicator that can be docked to any side of the window) - I should compare this to the SQL 2008 Central Management Server, where I set my production servers to be RED status bar for warning purpose
• Search Table or Database Data
• Uppercase/Lowercase keywords and Proper Case Database Object Names
• Sending feedback directly from SSMS.
• Import and Export options.
• Delete Query history log files older than user settable age.  

SSMS Tools Pack (for SQL Management Studio 2005/2008)

I want to share a tool (FREE) that I have used extensively to help in where SSMS lack in features (such as Generate Insert Statements, run Custom Scripts, etc...) - SSMS Tools Pack

The author released the SQL 2008 version just recently, and it has worked beautifully just like the 2005 version.

The SSMS Tools Pack does NOT work for SSMS 2005 versions before SP2 anymore.
---------------------------------------------------------------
SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

It contains a few upgrades to the IDE that I thought were missing from the Management Studio:

Query Execution History (Soft Source Control) and Current Window History.

Search Database Data.

Uppercase/Lowercase keywords.

Run one script on multiple databases.

Copy execution plan bitmaps to clipboard.

Search Results in Grid Mode and Execution Plans.

Generate Insert statements for a single table, the whole database or current resultsets in grids.

Text document Regions and Debug sections.

Running custom scripts from Object explorer's Context menu.

CRUD (Create, Read, Update, Delete) stored procedure generation.

New query template.