Getting sp_help to work with schemas from keyboard shortcuts
I love the keyboard shortcuts but in writing demos for
SQLBits I've been getting very frustrated that I can't select some text for an
object that is schame based and then use the keyboard shortcuts to get
things like table definitions, etc.
The issue is that when you select Person.Person, is this added to the end of
the stored proc call as follows
sp_help Person.Person
This obviously fails because of the period what should executed is
sp_help 'Person.Person'
But there is no way to get SSMS
to wrap your selected text in quotes. If you would like it fixed vote on this
connect item https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=349116&wa=wsignin1.0
In the mean time you can use the
following stored proc. Unfortunately you can't change sp_help in 2005 and
onwards and you can't access schema tables from sp stored procs as you could in
SQL 2000 and before. Once you have run the code below you can assign a shortcut
to it and now simply when highlighting Person it will figure out the schema. Be
warned if you have more than one matching object it will pick one of the
schemas.
use
master
go
create procedure
sp_help2
@object_name
varchar(100)
as
declare
@schema varchar(100)
declare
@sql nvarchar(1000)
set
@sql = 'select @schema
= schema_name(schema_id)
from '
+ db_name() + '.sys.objects
where name = @object_name'
exec
sp_executesql
@sql
, N'@schema
nvarchar(100) OUTPUT, @object_name nvarchar(100)'
,
@schema OUTPUT, @object_name
set
@object_name = db_name() + '.' + @schema + '.' + @object_name
exec
sp_help
@object_name
-