Marquer une procédure stockée comme objet système : la procédure stockée sp_MS_marksystemobject

Publié le par

Il peut être intéressant de pouvoir utiliser une procédure stockée dans n’importe quel contexte de base de données (sur la même instance SQL Server).
Voyons comment faire cela à l’aide de la procédure stockée système non documentée sp_MS_marksystemobject

Je cherchai à fournir à mes collègues développeurs SQL un moyen de générer des instructions CRUD (INSERT, SELECT, UPDATE, DELETE) de façon propre et générique.
Voici donc l’exemple de l’INSERT (je publierai les autres dans un billet à venir) :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE master
GO
 
CREATE PROCEDURE dbo.sp__generate_INSERT
    @_table_name sysname,
    @_nb_tab tinyint = 0
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @sql varchar(max)
    SELECT    @sql = CASE
                WHEN @sql IS NULL THEN REPLICATE(CHAR(9), @_nb_tab) + 'INSERT' + CHAR(9) + @_table_name +  CHAR(13) + CHAR(10) + REPLICATE(CHAR(9), @_nb_tab) + '(' +
                    CHAR(13) + CHAR(10) + REPLICATE(CHAR(9), @_nb_tab) + CHAR(9) + name
                ELSE @sql + CHAR(13) + CHAR(10) + REPLICATE(CHAR(9), @_nb_tab) + CHAR(9) + ', ' + name
            END
    FROM    sys.columns
    WHERE    object_id = OBJECT_ID(@_table_name)
    AND    is_computed = 0
    ORDER    BY column_id
     
    SET    @sql = @sql + CHAR(13) + CHAR(10) + REPLICATE(CHAR(9), @_nb_tab) + ')'
     
    PRINT    @sql
END

Vous remarquerez que je me place dans le contexte de la base de données système master, qui est la seule dans laquelle vous pouvez marquer des procédures stockées comme objet système.

Un autre requis pour pouvoir utiliser une procédure stockée marquée comme objet système dans n’importe quel contexte de base de données de l’instance en cours, est de faire commencer le nom de la procédure stockée par « sp ».
Rien ne vous empêche de marquer la procédure stockée comme objet système, mais lorsque vous exécuterez une telle procédure stockée (dont le nom ne commence donc pas par « sp »), SQL Server « feindra » de ne pas trouver celle-ci.

Le marquage se fait de façon extrêmement simple, mais n’est pas documenté :

EXEC sp_MS_marksystemobject 'sp__generate_INSERT'

Et le tour est joué : vous pouvez générer le script d’INSERT en exécutant tout simplement :

1
2
3
4
USE maBD
GO
 
EXEC sp__generate_INSERT 'maTable'

Bonne génération !

ElSüket ;)

5 réflexions au sujet de « Marquer une procédure stockée comme objet système : la procédure stockée sp_MS_marksystemobject »

  4. Pour les droits je dirais qu’il faut être sysadmin, mais je ne peux pas le vérifier sur le champ :)
    Une telle procédure stockée ne peut être que dans la base de données master ;)
    Enfin en ce qui concerne le nom de la procédure, il est recommandé de ne pas les nommer comme tu le suggeres, pour cette raison justement ;)

  5. Intéressant ;-)
    Quel droit minimum doit avoir l’application (ou l’utilisateur) qui exécute ce genre de procédure stockée ?
    Par ailleurs, le fait que cette procédure agisse au niveau instance présente à la fois des avantages et des inconvénients.
    Par exemple lors des Backup/Restore d’une base utilisateur sur une autre instance il faudrait être attentif au fait que la procédure stockée (sp__generate_INSERT) n’est pas incluse dans la base restaurée.
    Aussi la contrainte de nommage (sp…..) ne facilite pas beaucoup la vie ! ça peut être source de confusion entre les procédures systèmes natives et celles crées par l’utilisateurs (Développeur, DBA,…)

    A+

Laisser un commentaire