Im Artikel "Running a master db residing stored procedure in current database context" beschreibt Mladen Prajdicand, wie man aus einer normalen Prozedur in der Master-Datenbank eine System-Prozedur machen kann. Das hat den Vorteil, dass sie aus jedem Datenbank-Kontext aus aufgerufen werden kann. Sie liest dabei immer die Informationen aus den Verwaltungstabellen der jeweiligen Datenbank. Er beschreibt dazu zwei Voraussetzungen, die am SQL Server 2005 notwendig sind:

  • Die Prozedur beginnt mit "sp_" und
  • sie wurde als Systemprozedur gekennzeichnet.

Letzteres geht am SQL-Server-2005 mit "EXEC sp_MS_marksystemobject ", ist aber nicht immer unbedingt notwendig, dazu später mehr. Ich gebe zunächst mal ein Beispiel, damit klar wird, was gemeint ist:

Wenn man zwei Tabellen gleichnamige anlegt, eine in der einen Datenbank, die andere in einer anderen, z.B. eine in der Master und die andere in der Nordwind, dann kann man beide Tabellen mit der gleichen Prozedur abfragen.

-- Tabelle YouAreIn in Master anlegen und mit "Master" füllen
create table master.dbo.YouAreIn (dbname sysname not null);
insert into master.dbo.YouAreIn (dbname) values ('Master');

– Tabelle YouAreIn in Nordwind anlegen und mit "Northwind" füllen
create table Northwind.dbo.YouAreIn (dbname sysname not null);
insert into Northwind.dbo.YouAreIn (dbname) values ('Northwind');
go
use master
go
create procedure dbo.sp_where_am_i
as
select dbname
from YouAreIn – hier keine Datenbank angeben
go
– Als Systemprozedur markieren
EXEC sp_MS_marksystemobject 'sp_where_am_i'

Jetzt kann man in unterschiedlichen Datenbank-Kontexten die Prozedure aufrufen und es wird automatisch die "richtige" Tabelle gelesen:

use master
exec sp_where_am_i

Das liefert als Ergebnis "Master". Während das untige Statement "Northwind" meldet:

use Northwind
exec sp_where_am_i

Spaßigerweise haben die Systemprozeduren noch einen Insider-Hack: Wenn man den Datenbanknamen vor die Systemprozedur stellt, dann wird sie in dem Kontext ausgeführt:

use master
exec Northwind..sp_where_am_i

Das liefert "Northwind"!

Keine Regel ohne Ausnahme…

Um abwärtskompatibel zu sein, gibt es auch hier eine Ausnahme… Auch ohne das Markieren als systemprozedur wird die Prozedur im Kontext der Datenbank ausgeführt. Die Bindung zu den Tabellen bleibt aber zu den Tabellen in der Master bestehen.

Beispielsweise liefert die folgende Prozedur (in der Master angelegt, nicht als Systemprozedur gekennzeichnet) immer den "richtigen" Datenbank-Kontext und immer den Inhalt der Tabelle aus der Master.

create procedure sp_where_am_i_zwei
as
select db_name() as [DB-Kontext],
dbname as [Tabelle-Aus]
from YouAreIn

Aber es gibt noch eine weitere Ausnahme: Wenn man lediglich vor hat aus den "alten" Systemtabelle, wie sysobjects, zu lesen, dann verhält sich die Prozedur, wie ganz oben beschrieben. Wenn man aber aus den "Neuen" liest, wie bspw. "sys.objects", dann muss sie "markiert" werden, um das Ziel zu erreichen….