Modèle de script via Template Explorer

Un outil qui est présent dans Microsoft SQL Server Management Studio et qui reste méconnu est le "Template Explorer". Il est disponible via le menu "Edit\Template Explorer". 

Le panneau "Template Explorer" s'ouvre et nous donne accès à une multitude de modèle de script, tous regroupés par thématique qui peut vous être utile si vous ne pouvez retenir la syntaxe de chacune des instructions et de leurs options.

On peut aussi bonifier cet ensemble de script en ajoutant nos propres répertoires et fichiers pour que ceux ci puisse convenir aux normes de l'organisation. 

Les nouveaux répertoires et fichiers seront sauvegardés dans le répertoire %ApplicationData%\Microsoft\SQL Server Management Studio\12.0\Templates\Sql\Database\

Par contre, il faut savoir que l'ordre dans lequel les répertoires et les fichiers sont affichés sont en ordre alphabétique, on préférera alors, par exemple, créer un répertoire "_Mes Scripts" pour que celui-ci soit toujours affiché en premier et facilement accessible.

Personnalisation des bases de données

Parfois, il peut être pratique d'avoir un modèle de base de données qui sera appliqué à toutes les nouvelles bases de données qui seront créées sur le serveur. Pour ce faire, il nous suffit de personnaliser la base de données système "model".

Lors de la création d'une base de données, une copie de la base de données "model" est créée avec le nom désiré. Par défaut, cette base de données est dépouillée de toute table, procédure stockée, fonctions, ... Mais si nous ajoutons des objets dans cette base de données, que ce soit une table, une procédure stockée ou autre, ceux-ci seront automatique disponible dans les nouvelles bases de données.

Par exemple, voici ce qui est disponible lors de la création d'une base de données par défaut

Maintenant, créons une table et une procédure stockée dans la base de données modèle.

Recommençons la première étape, supprimons et recréons une base de données et vérifions que les nouveaux objets sont bien présents

Et voila! Notre nouvelle base de données avec des objets préinitialisés.

Par contre, il faut faire attention à ne pas en abuser. Il faut se rappeler que, à chaque fois que SQL Server redémarre, une nouvelle base de donneés "tempdb" est créée à partir de la base de données modèle. Si celle-ci est volumineuse, tempdb le sera aussi.

L'utilisation générale qu'on en fait est de paramétriser la base de données avec les options désirés et ajouter des utilisateurs/groupes qui doivent avoir accès par défaut.

Référence: https://msdn.microsoft.com/en-us/library/ms186388(v=sql.110).aspx

Utilisation de la clause output

Lorsqu'on modifie les données d'une table, soit par l'ajout, la mise à jour ou la suppression d'enregistrement, il est souvent utile de connaitre quelles données ont été modifiée. Que ce soit pour être inséré dans une table d'audit ou juste pour valider le bon déroulement de notre requête, il existe une clause qui permet d'obtenir ces informations sans passer par un trigger ou tout autre méthode.

La clause OUTPUT nous permet d'obtenir, à l'écran ou de sauvegarder le résultat dans une table, les enregistrements qui ont été modifiés par la requête. Pour obtenir ces informations, deux pseudo-tables sont disponibles, les mêmes pseudo-tables que dans les triggers. La pseudo-table INSERTED contiendra les données insérées tandis que la pseudo-table DELETED contiendra les données supprimées.

Clause OUTPUT dans une requête INSERT

Auparavant, lorsqu'on désirait connaitre l'identifiant du nouvel enregistrement, on devait se fier à la variable système @@IDENTITY ou la fonction SCOPE_IDENTITY pour obtenir l'id du dernier enregistrement inséré. Si bien utilisée, cette méthode fonction fonctionne bien mais avaient certaines limitations, comme par exemple, si votre clef primaire n'est pas un champ identity ou si vous insérez plus d'une ligne à la fois

La clause OUTPUT dans une requête INSERT nous permet de palier à ces limitations et d'avoir plus de contrôle. Prenons le script suivant :

Lors de son exécution, les données insérées sont affichées à l'écran. Si on veut sauvegarder ces données, on peut ajouter la clause INTO.

L'ordre des colonnes dans lequel les colonnes de la pseudo-table apparaissent peut être défini comme dans une clause SELECT. Pour la table dans la clause INTO, on peut spécifier l'ordre des colonnes entre parenthèses après le nom de la table.

Clause OUTPUT dans une requête UPDATE

De la même façon, on peut utiliser la clause OUTPUT pour obtenir les données qui ont été modifié par une requête UPDATE. La clause OUTPUT nous retournera que les enregistrements qui ont été modifiés. Dans ce cas-ci, on reprends la requête ci-dessus, puis on modifie seulement les enregistrements dont l'id est impair.

Clause OUTPUT dans une requête DELETE

La requête DELETE n'est pas en reste non plus. La clause output peut être utilisée pour connaitre les enregistrements qui ont été supprimé, cette fois avec la pseudo-table DELETED pour connaitre les enregistrements qui ont été supprimés

Utilisation de la clause OUTPUT

À quoi bon cela peut servir? Outre le fait de faire afficher à l'écran les données modifiées, on peut maintenant utiliser ces informations pour mettre à jour une autre table ou renvoyer ces informations vers un autre système.

Dans le cas ci-dessous, nous nous en servons pour remplir une table d'audit temporaire qui pourra être utilisée plus tard. En plus des informations sur les enregistrements modifiés, on sauvegardera aussi le contexte dans lequel ils ont été modifiés, avec par exemple, le nom de l'utilisateur qui a modifié les données, l'identifiant de sa session, le nom du programme utilisé, ....

Référence: https://msdn.microsoft.com/en-us/library/ms177564.aspx (clause output)

Recherche dans une stored proc

Des fois, nous avons besoin de retrouver une ou des stored procs dans laquelle nous avons laissé un commentaire quelconque (comme par exemple, les “-- TODO: “) ou bien retrouver quelles stored procs utilise une table en particulier lorsqu'on utilise du SQL dynamique.

Il existe plusieurs façon d'obtenir la définition d'une stored proc, soit en utilisant la fonction OBJECT_DEFINTION (OBJECT_ID), soit en utilisant la vue INFORMATION_SCHEMA.ROUTINES ou en utilisant la vue système sys.sql_modules.

Dans ce cas-ci, on allons utiliser la vue système sys.sql_modules pour obtenir la définition des stored proc dans la base de données au lieu de INFORMATION_SCHEMA.ROUTINES étant donnée que cette dernière ne retourne que les 4000 premiers caractères.

En plus de la définition des stored procs de la base de données, la vue sys.sql_modules nous donne accès aux caractérisques des stored procs (créée ou non avec SCHEMABINDING, WITH RECOMPILE, ...) à l'exception du nom de la stored proc et de son schema, ce qui peut causer des désagrément.

Maintenant qu'on a ces informations, on veut restreindre le nombre d'occurrence retourné pour avoir que ceux qui contiennent le texte recherché. Dans notre exemple, on recherchera la ou les storeds proc qui impriment les erreurs (« print error »).

Nous avons donc trouvé la stored proc qui contient « print error ». Il nous reste à connaître son nom ainsi que son schema. Pour cela, on utilise les fonctions OBJECT_SCHEMA_NAME et OBJECT_NAME pour obtenir les informations qui nous manque.

Et voilà, nous avons donc trouvé le bon nom d'objet dont la définition contenait les mots « print error ».

Ceci dit, lorsqu'on cherche dans l'ensemble des définitions de la vue sql_modules, on voudra parfois restreindre la portée de notre recherche. La vue sys.sql_modules contient l'ensemble des définitions pour les objets de type P (SQL Stored Procedure), RF (Replication-filter-procedure), V (View), TR (SQL DML Trigger), FN (SQL scalar function), IF (SQL inline table-valued function), TF (SQL table-valued-function) et R (rule, old-style, stand-alone).

Pour limiter la portée et n'inclure que les définitions qui correspondent à un type d'objet en particulier, on joindra la vue sys.objects dans notre requête. En joignant ces deux vues, on avons donc accès au type de l'objet, mais aussi à d'autres informations qui peuvent nous aider à filtrer selon nos besoins, comme par exemple, si on voudrait obtenir les stored procs qui ont été modifiées depuis leur création.

Encore une fois, il nous manque des informations pour avoir le nom complet. On utilisera la fonction SCHEMA_NAME avec le champ schema_id pour obtenir le nom complet de l'objet.

Référence : https://msdn.microsoft.com/en-us/library/ms175081.aspx (sys.sql_modules)

                  https://msdn.microsoft.com/en-us/library/ms190324.aspx (sys.objects)