Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
It doesn't look like it works in SQL Server 2008, unfortunately. –  Petter Brodin Jun 5 '12 at 11:04
    
@PetterBrodin - Yes it does. I've tested it in 2008. Are you sure you changed the context back to mydb after marking it as a system object? –  Martin Smith Jun 5 '12 at 11:05
    
You, re right, I misunderstood your example. This works perfectly. Let's just hope that it works with later versions as well. Thanks a lot! –  Petter Brodin Jun 5 '12 at 11:25
add comment

Alternative: using dynamic SQL. And no, in controlled admin functionality, dynamic SQL is not the evil thing it's often made out to be.

USE master;
GO
CREATE PROCEDURE dbo.sp_find_table
    @db_name    SYSNAME,
    @table_name SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SELECT @sql = 'SELECT QUOTENAME(s.name)
      + ''.'' + QUOTENAME(t.name)
      FROM ' + @db_name + '.sys.tables AS t
      INNER JOIN ' + @db_name + '.sys.schemas AS s
      ON t.[schema_id] = s.[schema_id];';

    EXEC sp_executesql @sql;
END
GO

Now your call from any other database can be:

DECLARE @db SYSNAME = DB_NAME(DB_ID());
EXEC master.dbo.sp_find_table @db_name = @db, @table_name = 'floob';

Or simply:

EXEC master.dbo.sp_find_table 'tempdb', @table_name = 'floob';

You can even create a synonym in model (and all existing databases) like follows:

CREATE SYNONYM dbo.sp_find_table FOR master.dbo.sp_find_table;
GO

(Now you can take the prefix off of the above calls.)

While your call is one step more complex, the benefits of this approach:

  • you can put this procedure in any utility database you want, instead of mucking up master with your procedures. Dependence on non-system objects in master can be an issue if you migrate to a new server, for example.
  • you don't rely on undocumented, unsupported, and not-guaranteed-to-be-forward-compatible functionality.
  • you can call it for any database, from any database, without relying on the current database context.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.