Weitläufig versteht man als “Systemprozedur” in Microsoft SQL Server eine Stored Procedures, die in der master-Datenbank gespeichert wird und mit dem Präfix ‘sp_’ beginnt”. Alle Objekte in der master-Datenbank mit diesem Präfix können aus jeder beliebigen Datenbank aufgerufen und verwendet werden.”; auch das ist eine weit verbreitete Aussage, die so – pauschal – nicht richtig ist; oder anders ausgedrückt nicht immer das gewünschte Ergebnis liefert. Der folgende Artikel zeigt die Besonderheiten bei der Verwendung von Systemobjekten auf und welche Dinge zu beachten sind, wenn man mit gleichen Objekten in unterschiedlichen Schemata arbeitet.
sp_ = Systemprozedur?
Das Präfix sp_ steht nicht für “Stored Procedure” sondern für “special”. Gespeicherte Prozeduren (sowie Tabellen und Views), die in der master-Datenbank mit dem Präfix sp_ gespeichert werden, können von jeder Datenbank ohne Referenz aufgerufen werden, wenn keine lokale Version existiert! Ist eine solche Prozedur als Systemobjekt markiert, wird die Prozedur aus der master-Datenbank im Kontext der aufrufenden Datenbank ausgeführt. Die Namensvergabe für benutzerdefinierte Objekte wird nicht eingeschränkt. Eigene Objekte können jederzeit mit sp_ als Präfix gespeichert werden.
Die Abbildung zeigt einen Ausschnitt aus der master-Datenbank. Systemprozeduren können sowohl im [dbo]-Schema (siehe [dbo].[sp_MScleanupmergepublisher) als auch im [sys]-Schema gespeichert werden. In der Regel werden Prozeduren, die von Microsoft kommen mit sp_MS gekennzeichnet oder werden – wie bereits erwähnt – im [sys]-Schema gespeichert. Jedoch können auch eigene gespeicherte Prozeduren als Systemprozeduren verwendet werden. Die ersten drei Prozeduren wurden erstellt, um mir auf Konferenzen möglichst schnell z. B. eine Demodatenbank zu erstellen ([dbo].[sp_create_demo_db]). Während im [sys]-Schema keine eigenen Objekte gespeichert werden können, können im [dbo]-Schema eigene Tabellen / Views / Prozeduren hinterlegt werden.Durch die Besonderheit, dass Microsoft SQL Server bei Verwendung von Objekten mit dem Präfix [sp_] – auch – außerhalb des Kontext der aktuellen Datenbank in master nach einem solchen Objekt sucht, wurde schnell von “system procedure” gesprochen.
Aufruf von Objekten
Das Präfix [sp_] bedeutet lediglich, dass AUCH in der master-Datenbank nach einer Objektreferenz gesucht wird, wenn in der lokalen Datenbank ein entsprechendes Objekt nicht existiert. Aber auch im Kontext der aktuellen Datenbank gibt es Besonderheiten, die beachtet werden müssen; Schemata! Mit Hilfe von Schemata ist es möglich, dass Objekte mit gleichem Namen in unterschiedlichen sicherheitsrelevanten Bereichen einer Datenbank gespeichert werden können. Welches Objekte zuerst ausgeführt wird, hängt von folgenden Faktoren ab:
- Aufruf als voll qualifiziertes Objekt
- Aufruf als Objekt ohne Schemaangabe
Testumgebung
Mit dem folgenden Skript wird die Umgebung für die Demonstration der Aufrufkette erstellt. Ebenfalls wird ein Testbenutzer angelegt, unter dessen Kontext die Prozeduren später aufgerufen werden können.
USE master;
GO
-- Create obe procedure in master database
CREATE OR ALTER PROCEDURE dbo.sp_myproc
AS
BEGIN
SET NOCOUNT ON;
RAISERROR (N'I am located in database [master] and I am in schema [dbo].', 0, 1) WITH NOWAIT;
SET NOCOUNT OFF;
END
GO
GRANT EXECUTE ON [dbo].[sp_myproc] TO public;
GO
USE demo_db;
GO
-- Create a schema for the first procedure
IF SCHEMA_ID(N'test') IS NULL
EXEC sp_executesql N'CREATE SCHEMA [test] AUTHORIZATION dbo;';
GO
-- Create a test user for the demos
IF USER_ID(N'test_user') IS NULL
EXEC sp_executesql N'CREATE USER [test_user] WITHOUT LOGIN
WITH DEFAULT_SCHEMA=[test];';
GO
GRANT EXECUTE ON SCHEMA::[test] TO [test_user];
GRANT EXECUTE ON SCHEMA::[dbo] TO [test_user];
GO
-- Create two procedures in the demo database
CREATE OR ALTER PROCEDURE dbo.sp_myproc
AS
BEGIN
SET NOCOUNT ON;
RAISERROR (N'I am located in database [demo_db] and I am in schema [dbo].', 0, 1) WITH NOWAIT;
SET NOCOUNT OFF;
END
GO
CREATE OR ALTER PROCEDURE test.sp_myproc
AS
BEGIN
SET NOCOUNT ON;
RAISERROR (N'I am located in database [demo_db] and I am in schema [test].', 0, 1) WITH NOWAIT;
SET NOCOUNT OFF;
END
GO
Besondere Beachtung gilt dem Benutzer [test_user], dem das Schema [test] als Standardschema zugewiesen wird.
Suchreihenfolge
Beim ersten Test wird der Benutzer [test_user] die Stored Procedure [sp_myproc] aufrufen. Die Aufrufe unterscheiden sich dadurch, dass sie einmal ohne Angabe eines Schemas und anschließend MIT Schemaangabe ausgeführt werden.
USE demo_db;
GO
EXECUTE AS USER = 'test_user'
EXEC sp_myproc;
EXEC dbo.sp_myproc;
REVER
GO
Das Ergebnis des ersten Aufrufs führt zur Ausführung von [test].[sp_myproc]. Ohne die Angabe eines Schemas wird zuerst im Standardschema des Benutzers nach einem entsprechenden Objekt gesucht. Ist ein solches Objekt nicht vorhanden, wird im Schema [dbo] nach einem Objekt mit dem Namen gesucht. Ist das Objekt im Standardschema des Benutzers, hat dieses Objekt immer Priorität. Für das nächste Beispiel wird die Prozedur [test].[sp_myproc] gelöscht und die Ausführung erneut gestartet.
Da nun im Standard-Schema das Objekt nicht mehr vorhanden ist, wird – mit dem ersten Aufruf – im [dbo]-Schema nach einem entsprechenden Objekt gesucht. Sofern dort ein entsprechendes Objekt vorhanden ist, wird es ausgeführt. Bei “gewöhnlichen” Objekten wird nicht mehr weiter nach Objekten gesucht; bei der Verwendung des Präfix [sp_] jedoch wird – bei Nichtvorhandensein im aktuellen Kontext – zusätzlich noch in der master-Datenbank gesucht. Wird im Beispiel nun auch noch die gespeicherte Prozedur [dbo].[sp_myproc] in der lokalen Datenbank gelöscht, ergibt sich das nachfolgende Ergebnis:
Nun erfolgt in beiden Aufrufen das gleiche Ergebnis. Da ein korrespondierendes Objekt nicht mehr in der Benutzerdatenbank vorhanden ist, wird die Prozedur aus der master-Datenbank ausgeführt.
Problembeschreibung
Mit Hilfe von Systemprozeduren in der master-Datenbank lassen sich für DBA einige Erleichterungen erreichen, um z. B. schnell Informationen über die Beschaffenheit eines Indexes in einer Benutzerdatenbank abzurufen. Aus diesem Grund wird die nachfolgende Prozedur [dbo].[sp_IndexInformation] erstellt.
USE master;
GO
CREATE OR ALTER PROC dbo.sp_IndexInformation
@Object_Name SYSNAME ,
@Index_id INT = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT DB_NAME(DB_ID()) AS [database_name],
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS [object_name],
i.name AS index_name ,
i.index_id AS index_id ,
ps.fragment_count ,
ps.avg_fragmentation_in_percent ,
ps.avg_page_space_used_in_percent ,
ps.page_count ,
ps.record_count
FROM sys.schemas s
INNER JOIN sys.tables t ON ( s.schema_id = t.schema_id )
INNER JOIN sys.indexes i ON ( t.object_id = i.object_id )
CROSS APPLY sys.dm_db_index_physical_stats
(
DB_ID() ,
i.object_id,
i.index_id,
NULL ,
'DETAILED'
) ps
WHERE i.object_id = OBJECT_ID(@Object_Name)
AND
(
i.index_id = @Index_id
OR @index_id IS NULL
)
ORDER BY
t.name,
i.index_id;
SET NOCOUNT OFF;
END
GO
Anschließend wird in einer beliebigen Benutzerdatenbank eine Testtabelle mit zwei Indexen erzeugt. die anschließend mit Hilfe der gespeicherten Prozedur untersucht werden sollen.
USE demo_db;
GO
SELECT *
INTO dbo.messages
FROM sys.messages;
GO
CREATE UNIQUE CLUSTERED INDEX cuix_messages
ON dbo.messages
(
message_id,
language_id
);
GO
CREATE NONCLUSTERED INDEX nix_messages_severity
ON dbo.messages (severity);
GO
EXEC dbo.sp_IndexInformation
@Object_Name = N'dbo.messages',
@Index_id = NULL;
GO
Obwohl das Objekt mit seinen beiden Indexen angelegt wurde, wird von der gespeicherten Prozedur kein Datensatz zurück geliefert! Grund dafür sind verschiedene Objekte innerhalb der Prozedur. Die Objekte [sys].[schemas], [sys].[tables] und [sys].[indexes] sind die Spielverderber. Alle genannten Objekte sind datenbankspezifische Objekte und werden im Kontext der Datenbank verwendet, in dem sich das aufrufende Objekt (die gespeicherte Prozedur) befindet. OBJECT_ID(), DB_ID() und [sys].[dm_db_index_physical_stats] sind Systemobjekte, die nicht in einer Benutzerdatenbank gespeichert sind sondern in der Ressourcendatenbank von Microsoft SQL Server – sie können datenbankunabhängig aufgerufen werden und werden im Kontext der aktuellen Benutzerdatenbank verwendet.
Lösung
Die zuvor erstellte Prozedur [sys].[sp_IndexInformation] muss von einer “normalen” Prozedur zu einer echten Systemprozedur geändert werden. Mit Hilfe der – undokumentierten – Systemprozedur [sp_MS_marksystemobject] kann ein beliebiges Objekt in der master-Datenbank zu einem Systemobjekt hochgestuft werden. “Undokumentiert” bedeutet, dass die Funktionalität von Microsoft jederzeit eingestellt und/oder geändert werden kann!
[sp_MS_Marksystemobject] verwendet die Parameter @objname und @namespace, um ein Systemobjekt zu erstellen. Der Parameter @objname bestimmt das Objekt (voll qualifiziert), das zu einem Systemobjekt geändert werden soll; der Parameter @namespace ist nur relevant, wenn es gilt, einen Trigger (Server / Datenbank) in ein Systemobjekt umzuwandeln. Das nächste Skript zeigt die Umstellung sowie die Ergebnisse vor und nach der Umstellung:
-- make the special procedure a system procedure
USE master;
GO
EXEC sp_MS_marksystemobject
@objname = 'dbo.sp_IndexInformation',
@namespace = NULL;
GO
Wird die “Systemprozedur nun im Kontext der Benutzerdatenbank ausgeführt, erreicht man das gewünschte Ergebnis.
USE demo_db
GO
-- Execution of system procedure with results
EXEC sp_IndexInformation
@object_name = 'dbo.messages',
@Index_id = NULL;
GO
Herzlichen Dank fürs Lesen!
