2

I am using sp_helptext to list some stored procs instead of going through the ui. I am having a problem with the naming convention we have chosen to adopt.

I have a stored proc nameed [Test.FooBar].

Execute sp_helptext [Test.FooBar]

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54 The object 'Test.FooBar' does not exist in database 'Foo' or is invalid for this operation.

When I change the exceut statement to be fully qualified i get a different error

Execute sp_helptext [Foo].[dbo].[Test.FooBar]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Can one someone point me in the right direction to fix this. Thank you very much.

10

Maybe try:

EXEC Foo..sp_helptext 'dbo.[Test.FooBar]';

Still having problems with sp_helptext? Ok, stop using it - it's not the only way to get the object's definition.

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.[Test.FooBar]'));

Next, please stop putting . and other special characters within object names.

  • 4
    I think that second suggestion is the more important one ... – jcolebrand May 4 '11 at 3:07
  • How to get around the 8k limit of output for large db objects from the sqlcmd command line and not via ssms? – Gary Thomann Apr 27 '17 at 1:47
  • @Gary Powershell? Convert to XML? I mean if you have stored procedures that are > 8K and sqlcmd can't show > 8K, I'm not sure what you can do. I published a tip about this that you might find readworthy. – Aaron Bertrand Apr 27 '17 at 2:06
  • @AaronBertrand I was hoping this might have been solved by now. I prefer to mostly use the sqlcmd cli but dumping from ssms for large source is still the only way then. Thanks for your answer. – Gary Thomann Apr 27 '17 at 2:35
2

How about trying the below?

sp_helptext 'Test.FooBar'
  • Single and double qoutes still throw the invalid operation error – george9170 May 3 '11 at 17:10
  • Can you tell us SELECT @@version? The code I gave above works perfectly on a SQL Server 2005 SP3 box. Even the [] works perfectly for me. – Sankar Reddy May 3 '11 at 17:16
  • @Sankar Reddy: do you have a stored proc called test.foobar, or did it work for another stored proc? – gbn May 3 '11 at 17:18
  • Is Test.Foobar as stored procedure/function/View or a table? Can you check object type select * from sys.objects where name 'Foobar' – Sankar Reddy May 3 '11 at 17:18
  • @gbn works for both – Sankar Reddy May 3 '11 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.