Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

2 Answers

up vote 8 down vote accepted

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.

share|improve this answer
3  
I think that second suggestion is the more important one ... – jcolebrand♦ May 4 '11 at 3:07
up vote 1 down vote

How about trying the below?

sp_helptext 'Test.FooBar'
share|improve this answer
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
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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