Using the correct database when calling a system stored procedure in SQL Server 2008

I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity):

ALTER PROCEDURE sp_mysp
AS
IF (EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'mytable'))
BEGIN
    --Do stuff to the table
END
ELSE
BEGIN
    PRINT 'Table mytable does not exist'
END

The problem is that when I call the procedure from mydb, the Master table is used for the initial check, rather than the current database. If I write mydb.INFORMATION_SCHEMA.TABLES it works, but that's not really a solution, as it's defeating the whole point of maintaining this as a single SP rather than one SP for each DB.

Any ideas? I tried passing the DB name as a parameter and starting the SP with 'use @db_name', but apparently stored procedures don't allow use statements.

Thanks in advance for all help.

You need to mark your stored procedure as a system object to get the behaviour that you want (full example below tested as working on 2008 SP3)

USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

This is an undocumented approach and may not work in future versions.

StackExchange Database Administrators Q#18824, answer score: 6