Créer une procédure exécutable de toutes les bases et dans le contexte de la base courante

Laisser une réponse

Voici comment écrire une procédure stockée qui pourra être appelée depuis n’importe quelle base de données et s’exécutera dans le contexte de la base de données appelante. SQL Server les appellent « procédures marquées système », mais je préfère le terme de procédure génériques.

Il est parfois intéressant, notamment dans le cadre de procédures d’administration, de pouvoir exécuter une même procédure depuis n’importe quelle base de données et que cette dernière s’exécute dans le contexte de la base de données appelante, et non dans le contexte de la base dans laquelle elle réside c’est à dire une base centrale, en l’occurrence master. On dit de ces procédures qu’elle sont « marquées système ».
Pour réaliser cela, il faut trois conditions :
1) que cette procédure soit stockée dans master;
2) que le nom de cette procédure commence par « sp_ » (en minuscule)
3) que la procédure soit marquée comme procédure système à l’aide de la procédure sys.sp_MS_marksystemobject.
Notez bien la casse de cette procédure, car certains articles incomplets, la présente avec une casse différente inexacte…

DÉMONSTRATIONS

1 – une procédure centralisée qui ne s’exécute pas depuis n’importe quelle base
En exécutant ce script :

USE master;
GO
 
CREATE PROCEDURE TOTO
AS
SELECT DB_NAME();
GO
 
USE tempdb;
GO
 
EXEC TOTO;

Vous obtiendrez l’erreur :

Msg 2812, Niveau 16, État 62, Ligne 2
Procédure stockée ‘TOTO’ introuvable.

En effet impossible de trouver TOTO dans le contexte de tempdb. C’est normal, TOTO est une procédure comme les autres !
Procédure MS SQL Server non marquée système

2 Рune proc̩dure centralis̩e marqu̩e syst̬me
Voyons ce qui se passe en marquant la procédure comme étant un procédure « système » de SQL Server :

USE master;
GO
 
EXEC sys.sp_MS_marksystemobject 'TOTO'
GO
 
USE tempdb
GO
 
EXEC TOTO
GO

À nouveau nous avons l’erreur suivante :

Msg 2812, Niveau 16, État 62, Ligne 1
Procédure stockée ‘TOTO’ introuvable.

Mais cette fois la procédure est reconnue comme procédure système. Vérifions à l’aide d’une requête :



SELECT name, "object_id", "type", is_ms_shipped FROM sys.procedures 

 

name                                   object_id   type is_ms_shipped 

-------------------------------------- ----------- ---- ------------- 

TOTO                                   400525006   P    1

is_ms_shipped = 1 signifie que la procédure a été livrée en tant qu’objet appartenant à SQL Server (et non créé par le développeur…) !

Cette fois ci elle apparait bien comme procédure systèmes dans SSMS :
Procédure MS SQL Server marquée système

3 – une procédure centralisée qui s’exécute depuis n’importe quelle base
Essayons maintenant en préfixant la procédure avec sp_ (en minuscule) :

USE master;
GO
 
CREATE PROCEDURE sp_TOTO
AS
SELECT DB_NAME()
GO
 
EXEC sys.sp_MS_marksystemobject 'sp_TOTO'
GO
 
USE tempdb
GO
 
EXEC sp_TOTO
GO

C’est réussi !

--------------
tempdb

4 – Limites

sp_ doit être en minuscule. Si la casse d’installation de votre serveur est CS alors une procédure de nom SP_ ne sera pas considérée comme générique.

Vous pouvez aussi utiliser le préfixe xp_, mais il est, par principe, réservé aux procédures stockées étendues codées sous forme de DLL systèmes (non SQL CLR et en principe développées en langage C ou C++).

dbo est le seul schéma SQL dans laquelle doit figurer votre procédure. L’utilisation d’un autre schéma ne permet pas le généricité.

Il n’existe pas de procédure inverse destinée à faire revenir une procédure générique marquée système dans l’état d’une procédure ordinaire. Le plus simple est alors de la supprimer et de la recréer.

5 – Conseils

La procédure sys.sp_MS_marksystemobject n’est pas documenté et son code (vous pouvez l’obtenir avec : SELECT OBJECT_DEFINITION(OBJECT_ID('sp_MS_marksystemobject'));) donne explicitement certaines limites qui laissent à penser qu’elle ne sera pas toujours portée dans les futurs environnements SQL Server. Utilisez là donc avec parcimonie, lorsque plus rien n’est possible.

Pour faire « remonter » vos procédures générique en premier dans l’interface SSMS, vous pouvez utiliser le caractères $ comme première lettre de votre procédure après sp_.

6 – webographie
Kalen Delaney – Geek City : System Objects
Martin Catherall : This appears to be a system procedure..but it isn’t!
Creating Your Own SQL Server System Stored Procedures
elsuket : Marquer un procédure stockée comme objet système : la procédure stockée sp_ms_marksystemobject



-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire