How to create a service stored procedure and run it, like other sp _... utilities

Question

I need to create a stored procedure once and only in one place on my database server, but be able to run it from any database. However, I do not want to enter database.schema.procedure_nameevery time I run it. I would like to use it as a built-in procedure: sp_...is there any way to do this?

here is the procedure i am trying to create:

--actual procedure is more complex
CREATE PROCEDURE TestProcedure
AS

select * from sys.dm_exec_requests 

go

Here is how I would like to run it from SQL Server Management Studio:

EXEC TestProcedure
--or 
TestProcedure

however you will get this error:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'TestProcedure'.

everything works fine if I run it with:

EXEC master.dbo.TestProcedure
--or
master.dbo.TestProcedure

you can run system routines like this without errors:

EXEC sp_help
--or
sp_help

Answer 1

sp_, . SQL Server ( ) .

datbase , .

USE master

create proc [dbo].[sp_test] as
select db_name()

GO

USE YourDB

EXEC master..sp_test  /*Returns "master"*/
EXEC sp_test          /*Returns "YourDB"*/

Edit:

Cade, . , . SQL 2005+

EXEC sys.sp_MS_marksystemobject 'dbo.sp_test'