SQL Server Management Studio Tips

Question

SQL Server Management Studio Tips

Konstantin Taranov

SSCertifiable

Points: 6498
More actions
August 14, 2017 at 12:00 am

#324848

Comments posted to this topic are about the item SQL Server Management Studio Tips

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

LeeFAR SSCarpal Tunnel Points: 4644 More actions · Answer 1

Thank you for this great list! One keyboard shortcut I would like to add is CTRL+SHIFT+R. This will refresh the intellisense cache to allow that new object you just created to show up when you start typing!.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 2

LeeFAR - Monday, August 14, 2017 6:59 AM
Thank you for this great list! One keyboard shortcut I would like to add is CTRL+SHIFT+R. This will refresh the intellisense cache to allow that new object you just created to show up when you start typing!.

Thanks for feedback and request - I will add this shortcut tomorrow in github repo.

Kenny Jozi SSCrazy Points: 2028 More actions · Answer 3

Great tips
Thanks

SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Well done - great collection of useful info.

I will advise, however, developers avoid using GO for inserting sample data as it would be horrifically slow.

For example, instead of this:
INSERT INTO TestData(CreatedDate) SELECT GetDate() GO 10

I would strongly encourage people to do this:
INSERT TestData(CreatedDate) SELECT GetDate() FROM (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)

For more records than that I'd use a permanent or inline tally table (or larger group of dummy rows).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Rod at work SSC-Dedicated Points: 34006 More actions · Answer 5

These tips are really great. However, I was a little disappointed to find I could use the 2 system SP's I use most frequently (sp_tables and SP_columns), into the Keyboard Shortcuts for Favorite Stored Procedures. I put them there but once I tried executing them they failed. They expect parameters, which SSMS doesn't give me a chance to enter. I expected it to paste sp_tables into the query window and then wait for me to type in a portion of the name of the table I was searching for. That didn't happen. SSMS just runs sp_tables and then immediately complained because I didn't supply parameters.

So, I removed my added custom commands. What's the point of trying to use something when it won't let me?

Kindest Regards, Rod Connect with me on LinkedIn.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 6

Kenny Jozi - Monday, August 14, 2017 8:21 AM
Great tips
Thanks

Thank you for your feedback. Be free add some issues and pull requests: https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Tips.md

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 7

Alan.B - Monday, August 14, 2017 8:29 AM
Well done - great collection of useful info.
I will advise, however, developers avoid using GO for inserting sample data as it would be horrifically slow.
For example, instead of this:
INSERT INTO TestData(CreatedDate) SELECT GetDate() GO 10
I would strongly encourage people to do this:
INSERT TestData(CreatedDate) SELECT GetDate() FROM (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)
For more records than that I'd use a permanent or inline tally table (or larger group of dummy rows).

Yes, I completely agree with you. But this tip excellent works for simple demos.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 8

Rod at work - Monday, August 14, 2017 8:54 AM
These tips are really great. However, I was a little disappointed to find I could use the 2 system SP's I use most frequently (sp_tables and SP_columns), into the Keyboard Shortcuts for Favorite Stored Procedures. I put them there but once I tried executing them they failed. They expect parameters, which SSMS doesn't give me a chance to enter. I expected it to paste sp_tables into the query window and then wait for me to type in a portion of the name of the table I was searching for. That didn't happen. SSMS just runs sp_tables and then immediately complained because I didn't supply parameters.
So, I removed my added custom commands. What's the point of trying to use something when it won't let me?

If you have some mandatory parameters in stored procedure you must add it, for example I use sp_WhoIsActive for query shortcut like this:
sp_WhoIsActive @get_additional_info=1, @show_own_spid=1, @get_full_additional_info=1

Also could you share sp_tables and SP_columns code via github or post public link here?

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 9

Hello, where so I find the Favorite Stored Procedures?
How do I load them into SSMS?

drew.allen SSC Guru Points: 76985 More actions · Answer 10

You should be aware that the regex syntax changed dramatically from SQL 2014 to SQL 2016.

Also, keyboard shortcuts vary depending on the keyboard mapping scheme. Occasionally, my keyboard mapping scheme will have changed when I open up SSMS.

Also, for the stored procedure shortcuts, if you highlight text, it will supply that text as the first parameter. Obviously that only works if it only takes one required parameter.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 11

For the favourites Stored Procedure:- uspBulkUpload: the link is broken

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 12

francesco.mantovani - Monday, August 14, 2017 10:24 PM
For the favourites Stored Procedure:- uspBulkUpload: the link is broken

Thanks for issue, I will fix it tomorrow.

DarthJurious SSC Journeyman Points: 84 More actions · Answer 13

A trick for CMS...

A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.

You can register the CMS server just use a different value in Server Name. So if your CMS is SERVER\INSTANCE, you can register as SERVER,PORT.

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 14

This is a very neatly compiled article. Thank you for it.

One more thing I find all of us DBAs have to do is to run SSMS against SQL Servers in different domains using different credentials.
It is true you can right click on the shortcut and painfully enter the data every time you login.
But you can use the following tip to achieve that.

runas /savecred /user:USER-NAME "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

Save this shortcut in a different corner of your desktop and color code this shortcut using any of the options of the SSMS.

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA