I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with the same name doesn't exist in the user database.
EDIT - just proved that this works fine, as much for myself as anything. First I created your assembly and added the procedure necessary to repro (I'm not going to include all the bits here for brevity):
USE master;
GO
CREATE ASSEMBLY [Justin]
AUTHORIZATION [dbo]
FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F... even more data here ...;
AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO
Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:
CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
SET NOCOUNT ON;
EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO
Then I adjusted your repro code to call the wrapper from tempdb instead:
USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO
Results:
USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11
So this shows that you should be able to use a wrapper like this to be able to call CLR procedures, unreferenced, from other databases.
(However, I will suggest that in general this shouldn't be a goal - you should be properly defining references with three-part names where applicable.)
I'll confess I did make one change to your stored procedure though, to prevent Visual Studio from whining at me. I changed:
while (true)
To:
while (i <= 11)
But of course this change had nothing to do with the scoping issue.
Best Answer
From BOL, on
INFORMATION_SCHEMA.ROUTINES:Column name:
ROUTINE_DEFINITIONData type:
nvarchar(4000)Description: Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL. To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.
You can therefore view the rest by changing you script as follows:
The column aliased above as
FullDefinitionwill return the rest of the content asnvarchar(max).