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.