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.
Best Answer
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)
This is an undocumented approach and may not work in future versions.