Tuesday, July 28, 2009

Execute Most Frequent Queries with Keyboard Shortcut Keys

(If you are looking for commonly used keyboard shortcut keys for Sql Server Management Studio click here)

With my recent job, I am facing a problem to type frequently used queries again and again in query analyzer to execute. Searched for the best solution to avoid my typing practice and I found following ways to accomplish my task


  1. Write down queries that I need frequently and save them separately on my hard drive and to get them back, click on open file and connect it to my required Database Server and EXECUTE.
  2. Using Project
  • Create a new Project by selecting a template SQL SERVER SCRIPTS
  • Go to Solution Explorer, right click Queries folder and click on New Query.
  • Type your new query and save it.
  • Now we can use query by just double click on your required one.
Second method really helped me to write down my long query scripts and run these scripts with out retyping them, and without any trouble to browse and find these queries.

But for short queries and some long scripts too, which are more often used in my current projects. I tried to find out more quick ways and come a cross to Keyboard Shortcuts Keys to execute these most frequently used queries.


Is it possible in Sql Server Management Studio?


Yes, though with limitations, but it is possible to execute your frequently used queries with shortcuts keys.


Limits:


1. Execute queries by writing them in single line.
2. Max 32767 characters long query is possible
3. Parameters can’t be declared
4. Store Procedures, that doesn't require any parameter value or with default values are possible to execute


How to do?


1. Open Sql Server Management Studio
2. In menu bar click on tools and select options
3. Expend “Environment” and click on Keyboard




We can find that shortcut keys for three commonly used System Stored Procedures are already created.
• sp_help
• sp_who
• sp_lock


Lets create shourtcut key for our own query
(for adventureworks )


SELECT * FROM Production.Product

Press OK button to save your changes and restart Sql Server Management Studio. Now open a new query (Ctrl+N) for your adventureworks database and press Ctrl+3
Here is your result without typing any query in qurery analyzer
You can also execute stored procure by assigning it a shortcut key by above mentioned method. For demonstration we will create a commonly used stored procedure written by Tara Kizar, isp_ALTER_INDEX . Before assigning it a shortkut key, edit this stored procedure and assign a default value for @dbName.
To execute stored procedure just press Ctlr + 4

2 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Hey Aasim,

    You really shed some light in my workflow right now!

    I liked the Project way of doing this.

    Thanks,

    Leniel

    ReplyDelete

All suggestions are welcome