In KB #918992 and KB #246133, which describe how to transfer logins between instances, Microsoft recommends creating a user procedure in master called sp_hexadecimal (with no schema prefix, tsk tsk). Since I know there have been a lot of people who have created this procedure for themselves, in spite of the fact that the articles are intended only for versions prior prior to SQL Server 2008, I suggest you call it something else.

Why?

A user on asked on #sqlhelp why sp_helptext didn't work against his copy of sp_hexadecimal. Sure enough, sp_helptext did not work. I created the following procedure in master:

 USE [master];
GO
CREATE PROCEDURE dbo.sp_hexadecimal
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 1;
END
GO

Executing the procedure works fine, but any variation of sp_helptext (with or without specifying the proper schema prefix) did not:

EXEC sys.sp_helptext 'dbo.sp_hexadecimal';

OBJECT_DEFINITION() worked just fine, and gave me the text of the procedure no problem:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.sp_hexadecimal'));

So what gives?

I dug into sp_helptext to see where Msg 15197 was coming from. There were two spots, but the one in question makes It seem that sp_helptext (or some underlying engine logic) ignores or overrides the specified schema prefix.This error does not occur if I create a stored procedure called sp_hexadecimal under another schema, so it seems to be isolated to the dbo schema.

Making a copy of sp_helptext in the dbo schema led to the correct rendering of the source, just like OBJECT_DEFINITION() (this is what leads me to believe there is something deeper than sp_helptext itself that causes the problem). I filed a bug against sp_helptext, Connect #683234: sp_helptext treats non-system objects as system objects

But since several of the bugs and suggestions I've filed involving this object have been closed as fixed or generated very loud cricket sounds, I don't have a whole lot of faith it will be fixed. It's common knowledge that the sp_ system procedures are not going to be modified for new features (or any non-severe bugs).

Long story short: this all happens because sp_hexadecimal is what I call a quasi-system object.

Huh? What does that mean?

Well, it took quite a bit of unraveling, but as it turns out, sp_hexadecimal is kind of a hidden system procedure. In the master database, you can't see any evidence of its existence, as these queries all return NULL:

SELECT
  OBJECT_ID(N'sys.sp_hexadecimal'),
  OBJECT_ID(N'dbo.sp_hexadecimal'),
  OBJECT_ID(N'sp_hexadecimal');
 
SELECT [object_id] 
  FROM sys.all_objects
  WHERE name = N'sp_hexadecimal';

There is, however, a stored procedure called sys.sp_hexadecimal in the resource database, which of course isn't visible to you under normal circumstances.

You can get to the resource database of course using the DAC (that's a Dedicated Administrator Connection, not the other DAC, a Data-Tier Application, even though now in Denali they're trying to shift the name to Diagnostic Connection for Database Administrators – so now it's a DCDA, I guess). But I like this old trick of attaching a copy of the resource database as another name. This way I don't have to remember to connect via DAC, be local on the server, etc. when I feel like sniffing around.

I loaded up a copy of the resource database, and sure enough, I found the object. But from there, the trail runs cold pretty quickly. There is a row in sys.all_sql_modules, but the definition is NULL, and OBJECT_DEFINITION() comes back empty as well:

SELECT
    o.[object_id],
    [schema]   = SCHEMA_NAME(o.[schema_id]),
    o.name,
    module_def = m.[definition],
    obj_def    = OBJECT_DEFINITION(o.[object_id])
  FROM sys.all_objects AS o
  INNER JOIN sys.all_sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE o.name = N'sp_hexadecimal';

Results:

I also can't execute the stored procedure there:

EXEC sys.sp_hexadecimal;

While I don't have a whole lot of concrete answers for you, the moral of the story is, whenever you create procedures named sp_% – even when Microsoft recommends it – you could find yourself tripping over these quasi-system objects.