Custom key board mapping in Sql Server Management Studio to get the results of any table

Here is a simple trick to get the results of any table with custom key mapping in SSMS. With this we can get rid of writing the different Select queries to get the records and can save few key strokes. Below 3 steps will do the trick.

step 1:  Create a stored procedure which accepts the table name, no of rows to be returned and the order of the results

1: CREATE PROCEDURE [dbo].[GetAnyTableRowsInSSMS]
2: (
3: @TableName VARCHAR(20),
4: –No of rows to be returned in the result set
5: @NoOfRows SMALLINT=5,
6: –‘a’ for ascending, ‘d’ for descending
7: @Order VARCHAR(4) =’a’
8: )
9: AS
10: BEGIN
11: IF @Order=’a’
12: SET @Order =’asc’
13: ELSE
14: SET @Order=’desc’;
15: DECLARE @ResultQry NVARCHAR(100);
16: SET @ResultQry= ‘SELECT TOP ‘ + CAST (@NoOfRows AS VARCHAR(5))+ ‘ * FROM ‘ + @TableName + ‘ ORDER BY 1 ‘ + @Order
17: EXEC SP_ExecuteSql @ResultQry
18: END

Step 2: Bind this stored procedure to SSMS custom key as in the below screenshot

Step 3: Execute in the query analyzer with the required parameters

Here we got top 2 rows from user table in ascending order. Like wise by pressing ctrl+5 on “users” will get the top 5 results in ascending order as we have given no of rows as 5 and order as ascending by default.

Points to Ponder:

1. Place this stored procedure in some test database rather the original database to avoid accidental copy into production.
2.  The results will be sorted by considering the first column the table. You can change this by passing the column name also as the parameter to the Stored Procedure.
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s