Wednesday, March 24, 2010

SQL Server Management Studio Tips And Tricks

This week I decided I wanted to take a step back from my performance tuning series and present material that DBAs and developers may not know.  I will be focusing on SQL Server Management Studio (SSMS) tips and tricks. 

Keyboard Shortcuts:

SQL Server Management Studio allows keyboard shortcuts that can help you save time and increase efficiency.  Keyboard shortcuts allow you to execute TSQL commands at the push of a button. The best part about keyboard shortcuts is the shortcut can be used to pass highlighted text as parameters.  I will start with some of the built-in keyboard shortcuts.

To open the keyboard shortcuts menu, click Tools –> Options –> Expand Environment –> Click Keyboard.  Now you will note that keyboard shortcuts are loaded by default.  Some of the defaults are sp_help (Alt+F1), sp_who (Ctrl+2) etc… You can implement just about any code you want in the keyboard shortcut.  For example, you can put the query “select * from sys.dm_exec_requests” directly into the text box.  The beauty of this is you can open a new query window and just hit the shortcut keys to execute the query.  This saves time and makes life a bit easier.  Below is a screenshot of my shortcut list, including the query I posted above.

image

Well that is all good and nice but what else can shortcuts do.  The best part about shortcuts in my opinion is they can actually be used to execute stored procedures and supply highlighted values as parameters.

Lets run sp_help but we will never type the text sp_help.  Open a new query window and create a new table.

CREATE TABLE dbo.MyTable(ID INT);

Now type MyTable in the query window and highlight the text.  Once the text is highlighted, hold Alt and press F1.  You should see the results of sp_help displayed for the highlighted object, “MyTable.”

image

Note: If you need to specify an object in a different schema, you have to use the two part name “schema.Object” and put it in single quotes.  For example, ‘dbo.MyTable’.

Now that is pretty cool right?  What I will show you next is even cooler!  I will execute a user defined stored procedure with parameters  Open the keyboard shortcut menu and assign the below stored procedure to a shortcut key. 

image

Open a new query window and create the below stored procedure.

CREATE PROCEDURE dbo.KeyBoardShortcut(@Id INT,@Col CHAR(1))
AS
BEGIN
    SELECT @Id,@Col
END
GO

Now type the text below and highlight it.  Once highlighted, hit Ctrl+F1. (Note: If you assigned the procedure to a different shortcut, you will need to use that shortcut)

1,'a'

image

As you can see, the stored procedure was executed with our highlighted parameters!  This is a awesome feature that can save a lot of time, especially when you have custom code that you need to access frequently.  This increases efficiency and puts more information at your fingertips.  One of my favorite shortcut keys is sp_helptext.  You can use create a shortcut for sp_helptext and highlight any procedure, function, view, trigger, or stored procedure and get the create script of that object.

Listing All Table Columns

This is a request that I see very often.  The request usually revolves around developers who have a lot of columns in their table and have to select from most of them.  As you can imagine, typing each column name can be a very tedious process. There are two methods that I use to accomplish this task, when I am feeling a little lazy…… BUT the key is to work smarter not harder right :^) !

The first method is to right-click the table click script table as –> SELECT To –> New Query Window.  Voila we now have a select query that lists all the columns in the table.  You can perform the same steps to get a list of all columns in a view.

The next method is to expand the table –> Drag the columns folder into the query window.  This will generate a column delimited list of the column names.

Scripting Multiple Objects

I am sure many of you have tried to script multiple objects from Management Studio, but have had little luck.  Now, we could choose to right-click the database –> tasks –> generate scripts, but where is the fun it that?  This is an easier way that does not make you jump through hoops or follow a wizard.  The trick is to click on the folder containing the objects you want to script and then look at the Object Explorer Details pane.  Inside the Object Explorer Details pane you can Ctrl or Shift click multiple objects.  Once selected, you can right-click –> Script <Object Type> AS –> Create –> To New Window.

image

Creating a TSQL Toolkit

One of the most underrated or unknown features of SQL Server Management Studio is Template Explorer.  You may be thinking that template explorer is used specifically for TSQL templates, but I will show you a couple of ways to make Template Explorer function as a TSQL Toolkit.  Click CTRL+ALT+T or go to view –> Template Explorer to open the window.

image

Now this does not look that impressive from the get, but trust me it can and does get better. Right-click SQL Server Templates and choose New –> then click folder.  Give the folder a name. I will be using the name Test_Scripts.  Next create a new template in the Test_Scripts folder.  I named the new template sys.dm_exec_requests.  Your template explorer should now look like this.

image

Okay, well that is great…… How does this help me?  Well lets add some TSQL code to that new template.  Once we add code to the template, We can then drag the template into any query window and SSMS will automatically post the code into the open query window.  Basically, we can use Template Explorer as our personal Toolkit.  We can create any folder structure we want and SSMS does a great job of keeping all our scripts organized.  Let’s see this in action.

Right-click the sys.dm_exec_requests template and choose edit.  Paste the code below and click save.

SELECT * FROM sys.dm_exec_requests

Open a new query window and drag the sys.dm_exec_requests  template into the query window. Voila!!!!!!! the TSQL is automatically scripted into our new query window.  As you can see, we can use the template explorer to save our scripts and make them easily accessible.  This alleviates our need to open windows explorer or browse the file system for our scripts.  Plus al the scripts are saved and managed in one place.  If you need to copy your scripts out of template explorer, the actually already exist on the file system.  The directory will be within your documents.  On my SQL Server 2008 instance, my templates are stored here, C:\Documents and Settings\ahaines\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql.  I don’t know about you, but I for one love this feature.  This gives me the ability to quickly get data, without having to leave SSMS, plus I do not have to waste time searching the file system.  Give it a try and see what you think.

That is all the tips I have for now.  I hope that you have learned something new or any of these tips can help you.  Until next time, happy coding.

74 comments:

Brad Schulz said...

Wow! Adam, this is great stuff! I'll definitely be putting a lot of these Tips and Tricks to use. I tend to have gotten into a rut with SSMS, and haven't really taken the time to explore many of its features, so this article was perfect.

Thanks!

Unknown said...

Awesome! What a time saver. I've setup shortcuts and templates that I will be using daily. Thanks Adam and keep 'em coming!

Unknown said...

I have also heard about the way of repair outlook pst

Joshua Smith said...

Thanks for your help in describing this. Great review! Also turn your attention that it needs to search for mobile software development companies if you need android apps development.

Joshua Smith said...

Thanks for great article. Let me mention about homeowners insurance cost that are from home insurance companies. Save on free online rates on homeowners insurance.

Joshua Smith said...

Thank you for posting this great info. You have great possibility to get info about casino affiliate programs. The most common gambling programs such as acelive and great poker rooms such as party poker affiliate program.

Yard Management solutions said...

Hi,

Wow so nice! Imaging Services are a vital part of the growth of any business. New workstation assets are introduced to businesses as old equipment is deprecated and removed, or as new employees are hired as the workforce grows. Keep it working every day.

Hoyt Velasquez said...

These are very useful tips for SQL server management. They are indeed very helpful. Thank you for posting.

Clarissa Lucas said...

A very informative article in managing SQL servers. Thanks for sharing.

sai said...

Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.
angularjs training in chennai | angularjs2 training in chennai | angularjs4 Training in Chennai | angularjs5 Training in Chennai

pooja said...

Thanks for your informative article, Your post helped me to understand the future and career prospects & Keep on updating your blog with such awesome article.

Blueprism training in Chennai

Blueprism training in Bangalore

Blueprism training in Pune

Blueprism online training

Blueprism training in tambaram

gowsalya said...

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
DevOps online Training|DevOps Training in USA
Devops Training in Chennai

Devops Training in Bangalore

nilashri said...

Just stumbled across your blog and was instantly amazed with all the useful information that is on it. Great post, just what i was looking for and i am looking forward to reading your other posts soon!

Data Science training in kalyan nagar | Data Science training in OMR
Data Science training in chennai | Data science training in velachery
Data science training in tambaram | Data science training in jaya nagar

afiah b said...

Thank you so much for a well written, easy to understand article on this. It can get really confusing when trying to explain it – but you did a great job. Thank you!

java training in chennai | java training in USA

Anonymous said...

I am really impressed with your efforts and really pleased to visit this post.

angularjs-Training in velachery

angularjs Training in bangalore

angularjs Training in bangalore

angularjs Training in btm

angularjs Training in electronic-city

Anbarasan14 said...

Thanks for sharing this information. This is really useful. Keep doing more.

Franchise Business In India
Education Franchise
Computer Education Franchise
Education Franchise India
Computer Education Franchise In India
Language Education Franchise
Spoken English Franchise In India

sandhiya said...

I am really enjoying reading your well written articles.
It looks like you spend a lot of effort and time on your blog.Keep Doing.
Data Science Certification Bangalore
Data Science in Bangalore
data analyst training in bangalore
data analytics institute in bangalore
data analysis courses in bangalore

Vicky Ram said...

nice post thanks for sharing

Article submission sites
Education

rose said...

Just stumbled across your blog and was instantly amazed with all the useful information that is on it. Great post, just what i was looking for and i am looking forward to reading your other posts soon!
Microsoft Azure online training
Selenium online training
Java online training
Python online training
uipath online training

spot said...

super your blog
andaman tour packages
andaman holiday packages
web development company in chennai
Math word problem solver
laptop service center in chennai
Austin Homes for Sale
andaman tourism package
family tour package in andaman

anusha said...




AngularJS Training in Chennai AngularJS Training in Chennai at BITA Academy. We are Best AngularJS Training Institute in Chennai. Our AngularJS training courses are taught by Experts.

Aruna Ram said...

Thank you for developing this fabulous information and this is very useful for me. I liked your post and keep doing...!
Spark Training in Chennai
Spark Training Fees in Chennai
Appium Training in Chennai
Tableau Training in Chennai
Oracle Training in Chennai
Oracle DBA Training in Chennai
Linux Training in Chennai
Embedded System Course Chennai
Excel Training in Chennai
Spark Training in Vadapalani

impressbss said...


Thanks for sharing this blog
web designing company in chennai

Ram Niwas said...
This comment has been removed by the author.
easylearn said...

Hi,
Good job & thank you very much for the new information, i learned something new. Very well written. It was sooo good to read and usefull to improve knowledge. Who want to learn this information most helpful. One who wanted to learn this technology IT employees will always suggest you take Hadoop training in btm. Because data science course in pune is one of the best that one can do while choosing the course.

Vijiaajith said...

nice
interview-

questions/aptitude/permutation-and-

combination/how-many-groups-of-6-persons-can-be-

formed


tutorials/oracle/or

acle-delete


technology/chrom

e-flags-complete-guide-enhance-browsing-

experience/


interview-

questions/aptitude/time-and-work/a-alone-can-do-1-

4-of-the-work-in-2-days


interview-

questions/programming/recursion-and-

iteration/integer-a-40-b-35-c-20-d-10-comment-about-

the-output-of-the-following-two-statements


kumar vihaan said...

Thirdly the scalar capital co-founder brought;
“certain use instances can take off that don’t require perfect scaling e. G. Defi.”
Defi has been touted because of the fact the destiny of decentralized finance and a machine that is extended past due in an international in which banks are drowning in their very non-public money owed. Despite the ethereum charge promote off, Defi markets keep developing.As Kumar Vihaan, there is nothing to be concerned about scaling because Ethereum Scale itself to be able to handle the increased transaction and reduce the load on the main chain by moving the bulk of transactions to a second layer.

raju said...

nice post...!
brunei darussalam hosting
inplant training in chennai

raju said...


nice blogs...!
dominican republic web hosting
iran hosting
palestinian territory web hosting
panama web hosting
syria hosting
services hosting
afghanistan shared web hosting
andorra web hosting
belarus web hosting

dras said...

Nice...post...
Australia hosting
Bermuda web hosting
Botswana hosting
mexico web hosting
moldova web hosting
albania web hosting
andorra hosting
armenia web hosting
australia web hosting

dras said...

Excellent ..post
denmark web hosting
inplant training in chennai

shree said...

nice post...
luxembourg web hosting
mauritius web hosting mongolia web hosting
namibia web hosting
norway web hosting
rwanda web hosting
spain hosting
turkey web hosting
venezuela hosting
vietnam shared web hosting

ammu said...

excellent blogs.....!!!
chile web hosting
colombia web hosting
croatia web hosting
cyprus web hosting
bahrain web hosting
india web hosting
iran web hosting
kazakhstan web hosting
korea web hosting
moldova web hosting

dras said...

Very useful post...
python training in chennai
internships in hyderabad for cse 2nd year students
online inplant training
internships for aeronautical engineering students
kaashiv infotech internship review
report of summer internship in c++
cse internships in hyderabad
python internship
internship for civil engineering students in chennai
robotics course in chennai

nivetha said...

goood...nyc..
internships for cse students in bangalore
internship for cse students
industrial training for diploma eee students
internship in chennai for it students
kaashiv infotech in chennai
internship in trichy for ece
inplant training for ece
inplant training in coimbatore for ece
industrial training certificate format for electrical engineering students
internship certificate for mechanical engineering students

VISHAL said...
This comment has been removed by the author.
Trishana said...

Hi...
Such a time saving information you have shared. I am habituated with some of the tricks but with your articles i came up with some of new tricks. Thanks for sharing this. Keep posting such a useful things.

download movies said...

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

download movies said...

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

download movies said...

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

download movies said...

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

download movies said...

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

https://casinopokerreviews.com/

download movies said...

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

download movies said...

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

download movies said...

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

https://https://casinositesreview.com//

download movies said...
This comment has been removed by the author.
download movies said...

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

download movies said...

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

download movies said...

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

https://casinositesreview.com//

karthickannan said...

nice.....
coronavirus update
inplant training in chennai
inplant training
inplant training in chennai for cse
inplant training in chennai for ece
inplant training in chennai for eee
inplant training in chennai for mechanical
internship in chennai
online internship

Arunvijay said...

Exelene post..

Coronavirus Update
Intern Ship In Chennai
Inplant Training In Chennai
Internship For CSE Students
Online Internships
Internship For MBA Students
ITO Internship

Paari said...

Awesome
Intern Ship In Chennai
Inplant Training In Chennai
Internship For CSE Students
Coronavirus Update
Online Internships
Internship For MBA Students
ITO Internship

Anonymous said...

news

Anonymous said...

daily fun

latchu kannan said...

i got benefited from this..do more ,share more

BEST ANGULAR JS TRAINING IN CHENNAI WITH PLACEMENT

https://www.acte.in/angular-js-training-in-chennai
https://www.acte.in/angular-js-training-in-annanagar
https://www.acte.in/angular-js-training-in-omr
https://www.acte.in/angular-js-training-in-porur
https://www.acte.in/angular-js-training-in-tambaram
https://www.acte.in/angular-js-training-in-velachery


nizam said...

It is an impressive one.
AngularJS training in chennai | AngularJS training in anna nagar | AngularJS training in omr | AngularJS training in porur | AngularJS training in tambaram | AngularJS training in velachery


divya said...

Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.fine do better.
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai

subha said...

Nice Blog ! It was really a nice article and i was really impressed by reading this. Thanks for sharing such detailed information good work
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai

TIC Academy said...

Great Blog. Thnaks.
SAP Training in Chennai
Java Training in Chennai
Software Testing Training in Chennai
.Net Training in Chennai
Hardware and Networking Training in Chennai
AWS Training in Chennai
Azure Training in Chennai
Selenium Training in Chennai
QTP Training in Chennai
Android Training in Chennai

rocky said...

you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
Python Hyderabad

Python Training in Coimbatore
Python Training in Chennai

Python Training in Training

Python Training in Bangalore

Aishu said...

Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information.

IELTS Coaching in chennai

German Classes in Chennai

GRE Coaching Classes in Chennai

TOEFL Coaching in Chennai

spoken english classes in chennai | Communication training

hema said...

thanks for sharing such a wonderfull information.great article.
Web design Training in Chennai

Web design Training in Velachery

Web design Training in Tambaram

Web design Training in Porur

Web design Training in Omr

Web design Training in Annanagar

Alwin said...

German classes in Chennai
French Classes in Chennai
Spoken English Classes in Chennai
Spoken Hindi Classes in Chennai
Language Academy

abishamary said...

Nice article thanks for sharing the great information ......!
Ethical Hacking online training
PHP course in Chennai
Dot net course in Chennai

Keerthi55 said...

Hey, thanks for the blog article.Really thank you! Great.
MuleSoft online course
MuleSoft onlinetraining from india

Reshma said...

Wonderful post and more informative!keep sharing Like this!
Full stack developer course in Gurgaon
Full stack developer course in Hyderabad
Full stack developer course in Delhi

Hussey said...

Extraordinary Blog. Provides necessary information.
best selenium training center in chennai
​​best training institute for selenium in chennai

mindmade said...

Hi Dude

Debugging inside SQL Server has come a long way nowadays . SQL Demystified explains how to use SQL (Structured Query Language)--the ubiquitous programming language for databases through theses kind of articles . Thanks for sharing

Creative Point said...

Debugging inside SQL Server has come a long way nowadays . SQL Demystified explains how to use SQL (Structured Query Language)--the ubiquitous programming language for databases through theses kind of articles . Thanks for sharing

123webdesigncoimbatore said...

wonderful

Designpluz web said...

Thanks for sharing the valuable information. Keep Blogging!!!
Logo Design Company in Coimbatore

Soulpay Communication - Digital Banking Services said...

I would like to thank you for the efforts you had made for writing this awesome article. Soulpay provides multiple digital banking services like Retailer Fintech Store, aeps cash withdrawal, digital banking services, bbps service

vcube said...

I appreciate you providing this useful site because it gave me technical information. Keep publishing.
Java Fullstack Course In Hyderabad

iteducationcentre said...

Great Post.Thanks for sharing.
SQL Course in Pune