How to use sp_helptext with schema without quotes (for query shortcuts)

Question

All my procedures, views are in schema xx. I know I have to use quotes in this case and when I run

exec sp_helptext 'xx.my_procedure'

it works fine.

However I would love to use query shortcuts (I am using SSMS 16) for sp_helptext.

When I select text and run shortcut I am getting:

Incorrect syntaxt near '.'

which is obviously due to missing quotes in selected text.

Is there a way to workaround this?

Probably something that could temporarily add quotes via simple shortcut? Or maybe some other procedure that would nest sp_helptext and fix the input?

Answer 1

Maybe there are another approaches for achieving your target,

but mine is as following:-

Create a procedure that executing SP_HELPTEXT after override it.

assuming the scema name is XX

Create procedure usp_helptext (@myObject varchar(255))
as
begin
declare @Query nvarchar(1000)
set @Query = 'exec sp_helptext ''XX.' + @myObject + ''''
print @Query
exec (@Query)
end

and execute the created proecure as next:-

exec usp_helptext proc_name.

Finally use a shortcut for usp_helptext rather than sp_helptext.