SSMS Custom Keyboard Query Shortcut With(NoLock)

I have a custom Keyboard Query Shortcut in SSMS (2008) with the following t-sql...

SELECT TOP(50) * FROM 

This allows me to simply type in a name of a table and press my keyboard shortcut to get the top fifty items from that table. The problem I have is that I like to query using WITH(NOLOCK) so the records don't get locked. I haven't figured out how to enter T-SQL into the Keyboard Query shortcut where the input is in the middle of 开发者_开发知识库the statement. How can I incorporate a NoLock without resorting to using a stored procedure?

It can get a bit messy, but you can use sp_executesql to avoid relying on a stored procedure being defined:

sp_executesql N'EXEC(''SELECT TOP(50) * FROM '' + @input + '' WITH(NOLOCK)'')', N'@input varchar(max)', 

I have developed SSMSBoost ssms add-in - it has autoreplacements feature. You can define there:

Replacement token: "selt50"

Replacement string:"SELECT TOP(50) * FROM # WITH(NOLOCK)"

# tells there cursor will be placed, after replacement is done

so, when you then type

selt50 and_press_SPACE

selt50 will be replaced by

SELECT TOP(50) * FROM # WITH(NOLOCK)

with cursor blinking instead of #

ssmsboost comes with standart autoreplacements:

sel ->select * from

upd ->update # set where

deci-> declare @# int

you can certaily re-define them it is generally well-done add-in with several very useful tools for ssms (preferred connections, code navigation etc)

Create a stored procedure sp_Top50 in master and assign the shortcut to that SP. The sp_ prefix makes SQL Server to look for the stored procedure in master.

create procedure sp_Top50
  @T sysname
as
declare @S nvarchar(200)
set @S = 'select top(50) * from '+quotename(@T)+ ' with(nolock)'
exec (@S)