July 31, 2011

Use of Shortcut Key in SQL Server

We have shortcut key feature inside SQL Server but we generally don’t use it.
We can associate this feature with our frequent activities in query analyzer.
Like we select last 5 records descending order on identity key of table.
I have tried here to show you how can we do that.

First open the tools options window.

Tools >> Options >> Environment >> Keyboard

image

Now i have written stored procedure that will select last 5 records ordering on identity key.

CREATE PROCEDURE GetLast5Records
(
    @TName    NVARCHAR(100)
)
AS
BEGIN

    DECLARE @SQLString NVARCHAR(3000)
    DECLARE @IdentityColumn  NVARCHAR(100)

    SET @SQLString = N'SELECT @IdentityColumn = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, '+'''IsIdentity'''+') = 1 AND TABLE_NAME = '''+@TName+''''

    PRINT @SQLString

    EXEC sp_executesql
    @query = @SQLString,
    @params = N'@IdentityColumn NVARCHAR(100) OUTPUT',
    @IdentityColumn = @IdentityColumn OUTPUT

    SET @SQLString = 'SELECT TOP 5 * FROM '+ @TName +' ORDER BY ' + @IdentityColumn + ' DESC'

    EXEC(@SQLString)

END

Now in options keyboard window assign any not used key for this

image

Now I have table EMP and i want execute my shortcut on this table.
Select table name and simple press your assigned shortcut key in my
case it is Ctrl+3 you will see the output.

image

If shortcut key is not working restart management studio.

2 comments:

Anonymous said...

Nice post.

Anonymous said...

Good job.
I really enjoyed it.

Post a Comment