SQL Server Management Studio (SSMS) is the principal interface for working with Microsoft SQL Server. However, many subtleties of this powerful tool evade even the most seasoned professionals.
Here are a few of my favourite secrets to make life easier in SSMS for SQL Server 2012.
The Object Explorer Details pane doesn’t show up by default, but you’ll definitely want to know how to summon it.
Connect to your SQL Server instance, then in the object explorer pane drill down and highlight a container, such as Tables. Then click View => Object Explorer Details from the top menu bar.
Most people know that by highlighting an object (eg an SQL Agent job) and right-clicking, an option presents itself to generate a script of the said job. However it is only possible to script one object at a time – so for five jobs, repeat this process five times.
The Object Explorer Details pane allows you to select multiple objects – by using the Ctrl or Shift keys, and then right-clicking to select an option to script all objects in one hit.
Need to script 1000 objects? Do it this way and avoid RSI to your mouse-pinkie.
With the Tables container still highlighted, you’ll see that over in the Object Explorer Details pane along with the column containing the table names, there are some other uninspiring columns such as Schema, Policy Health State, and Create Date.
However, by right-clicking anywhere on the column headers, you can expose much more interesting hidden columns for inclusion. For the Tables container these include Row Count, File Group, and Data Space Used.
By left-clicking on any of these columns, you can sort the displayed objects by them. So questions like “What are the largest tables?” are now answerable in a couple of mouse-clicks.
For the Stored Procedures container a great column to add is Date Last Modified. Invaluable when you suspect that code has changed, but no one will admit to it.
There may be no such thing as a free lunch, but there are free third-party add-ins that provide new or enhanced functionality to SSMS.
You can improve SSMS’s code auto-completion; format code nicely; search a database and then search it again; and enhance query plan analysis.
If you’ve examined a long piece of T-SQL in SSMS, you’ve likely scrolled up and down through it many times. Perhaps a large block of variable declarations is at the top, and you need to keep referring to it in order to understand the code below.
Click Window => Split from the menu-bar at the top of SSMS, and your query is spilt into two independently scrollable panes. You can keep variable declarations in the top-most pane, whilst scrolling through the code in the lower pane
If you need to compare two different panes of T-SQL, don’t click back and forth between query tabs trying to remember what you saw. Just click Window => New Vertical Tab Group and both panes of T-SQL will co-exist together side-by-side for your viewing pleasure.
Can you remember the different style codes available within the CONVERT function? Me neither, let’s look them up.
We could minimise SSMS, open the browser, see we have new Gmail, open it, and view the video of a skateboarding duck that Bob sent. Go to Google, type in “T-SQL CONVERT”, and then scroll through the results to look for something relevant to click on.
Or we could just highlight CONVERT in SSMS, press the F1 key, and be taken straight to the authoritative information in the online documentation.
Imagine you’ve been emailed hundreds of values that you need to incorporate into a T-SQL query. You can copy and paste the values into a query pane in SSMS, but you’ll still need to manually navigate through each value and add a comma to them.
Wouldn’t it be nice if you could add characters and commands to each line of a query en masse?
Move the cursor to the starting position (in our example, just in front of the second value in the list), then press and hold down the left mouse button. At the same time press and hold down Alt and drag the cursor down until it arrives just in front of the last value in the list.
If your finger-origami is good, then a feint line will materialise.
Release both buttons. Now whatever character(s) you type will appear in that same position on each line. When you’ve finished typing, hit the Esc key to exit multi-edit mode.
Most people know that by dragging something like a column from the Object Explorer pane into a query pane, its name will appear within the query, removing the need to type it.
But if you expand a table in Object Explorer, and then drag the Columns container into the query pane, a nice comma separated list of every column in that table will be added to the query.
If you don’t need every single column in your query then it’s easy to delete ones you don’t require. This prevents typos and also avoids the dreaded shortcut SELECT * FROM.
About the author
Andy Hogg is a freelance consultant specialising in Microsoft SQL Server and associated technologies
Learn which capabilities chief information officer candidates need in 2024 if they want to land top roles and which macro factors…
Becoming a VR developer requires coding skills, the ability to work fast and a willingness to promote yourself. In a demanding …
Policymakers are considering the bipartisan National Quantum Initiative Reauthorization Act, which would continue quantum …
Huntress said in a blog post this week that the ConnectWise ScreenConnect flaws, which have come under attack, were ‘trivial and …
Cloud threat intelligence helps identify and analyze cloud-based threats, enabling security teams to better understand attacks …
Apple said its new PQ3 protocol for iMessage is the first of its kind and addresses both future threats from quantum computing as…
This network compliance checklist for remote work provides best practices on establishing remote policies and procedures, help …
A 12% drop in networking revenue contributed to the company’s overall revenue decline and its decision to cut 5% of its workforce.
Cisco has launched a SaaS product for applying policy controls to AI model-bound data and an Nvidia partnership to bolster Cisco …
Designing an efficient data center is no small feat. Review data center facility and infrastructure components as well as …
The rise in digital information is causing a shortage in data center storage space. Containerized data centers can be for …
There are regulated requirements to maintain data center equipment and functionality. ISO 14644 cleanroom standards lay out …
The data catalog specialist’s new tool examines four key criteria, including literacy and governance, in assessing an …
AI’s effectiveness is limited by data quality. Building strong data management and governance programs are crucial to handling …
Pipelines and platforms capable of managing volume and combining information from disparate sources in real time are key to the …
All Rights Reserved, Copyright 2000 – 2024, TechTarget
Privacy Policy
Cookie Preferences
Do Not Sell or Share My Personal Information
Leave a Reply