Quatre étapes sont nécessaires : créer un compte utilisateur, créer un profil, ajouter le
compte au profil et choisir les utilisateurs de msdb qui pourront accéder au profil (public,
privé). Ces étapes peuvent se faire par script de la manière suivante :
- Créer un compte utilisateur :
EXECUTE msdb.dbo.sysmail_add_account_sp
217
@account_name = 'monCompteMail',
@description = 'Compte Database Mail',
@email_address = 'grarestephane@hotmail.fr',
@replyto_address = 'grarestephane@hotmail.fr',
@display_name = 'Stéphane Grare',
@username='grarestephane@hotmail.fr',
@password='monmotdepasse',
@mailserver_name = 'smtp.live.com',
@port=25
- Créer un profil de comptes mail :
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'ProfilMail',
@description = 'Profile pour utilisateurs de Database mail'
- Assigner le compte au profil : cela se fait avec la procédure stockée
msdb.dbo.sp_sysmail_add_profileaccount. Il vous est possible de voir les
paramètres de chaque procédure stockée via le navigateur d'objets de Management
Studio :
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'ProfilMail',
@account_name = 'monCompteMail',
-- Désigne l'ordre dans lequel sont triés les comptes dans le profil
@sequence_number = 1
- Assigner le droit d'utilisation du profil à des utilisateurs : Il faut que les utilisateurs
puissent utiliser le profil. La procédure sysmail_add_principalprofil est là pour cela :
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'ProfilMail',
@principal_name = 'public',
@is_default = 1;
Ici, nous disons que le profil que nous avons créé peut être utilisé par les utilisateurs
appartenant au rôle 'public' pour la base de données MSDB. Nous aurions très bien pu
spécifier un utilisateur particulier.
- Envoyer un mail : Exécutons le script suivant.
EXEC msdb.dbo.sp_send_dbmail @recipients='grarestephane@hotmail.fr',
@subject = 'Test',
@body = 'MON PREMIER MESSAGE !!!!',
@body_format = 'HTML'
Si tout se passe bien, vous recevrez votre mail avec le message attendu. N'oubliez pas
de libérer le port 25 pour l'envoi de mail depuis votre serveur, il arrive que le serveur de
messagerie bloque tout simplement le mail...
Quelle que soit la possibilité choisie (script ou visuel), votre serveur SQL est maintenant
capable d'envoyer des emails. Nous allons maintenant voir comment utiliser cette possibilité à
travers un exemple.
218
Exemple : Utilisation d'un déclencheur DML avec un message de rappel par courrier
électronique. L'exemple suivant envoie un message électronique à une personne spécifiée
(Stéphane Grare) lorsque la table PRODUIT est modifiée.
USE Papyrus;
GO
-- S'il existe déjà un trigger nommer Rappel
IF OBJECT_ID ('vente.RappelMail', 'TR') IS NOT NULL
-- Alors on le supprime
DROP TRIGGER vente.RappelMail;
GO
CREATE TRIGGER RappelMail
ON vente.PRODUIT
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfilMail',
@recipients = 'grarestephane@hotmail.fr',
@body = 'Un message de votre trigger Rappel.',
@subject = 'Trigger Rappel';
GO
On test notre trigger :
USE Papyrus
UPDATE vente.PRODUIT
SET STKPHY = STKPHY -1
WHERE CODART = 'Z002'
219
FAQ
Comment obtenir la liste des tables d'une base de données ?
Vous avez beaucoup de possibilités pour connaître la liste des tables d'une base de données.
Nous vous recommandons d'utiliser les vues d'informations de schéma.
Use Papyrus
GO
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
Vous pouvez aussi passer par la procédure stockée sp_tables ou encore passez par les tables
systèmes.
Use Papyrus
GO
SELECT name FROM sysobjects WHERE type='U'
Comment connaître la liste des colonnes d'une table ?
Comme pour la liste des bases de données d'un serveur, SQL Server offre trois possibilités :
1 - La consultation des vues d'informations de schéma.
Use Papyrus
GO
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='PRODUIT'
2 - L'utilisation de la procédure stockée sp_columns
Use Papyrus
GO
EXEC sp_columns 'PRODUIT'
3 - L'utilisation de la procédure stockée sp_help
Use Papyrus
GO
EXEC sp_help 'vente.PRODUIT'
Comment lister l'ensemble des vues d'une base de données SQL Server ?
La liste des vues d'une base de données de SQL-Server est accessible grâce à une requête
sur les tables systèmes : sysobjects, syscomments et sysusers.
220
SELECT name
FROM sysobjects
WHERE type='V'
Mais il est recommandé d'utiliser les vues d'informations de schemas.
SELECT *
FROM information_schema.views
Comment lister l'ensemble des UDF d'une base de données SQL Server ?
La liste des fonctions définies par l'utilisateur de SQL-Server est accessible grâce à une
requête sur les tables systèmes : sysobjects, syscomments et sysusers.
SELECT name
FROM sysobjects
WHERE type='FN'
Comment lister l'ensemble des procédures stockées d'une base de données SQL
Server ?
La liste des procédures stockées de SQL-Server est accessible grâce à une requête sur les
tables systèmes : sysobjects, syscomments et sysusers.
SELECT name
FROM sysobjects
WHERE type='P'
On peut également utiliser la méthode des vues d'informations de schéma :
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
Ou encore, utiliser la procédure stockée : sp_stored_procedures
Comment lister l'ensemble des déclencheurs d'une base de données SQL Server ?
La liste des triggers de SQL-Server est accessible grâce à une requête sur les tables systèmes
: sysobjects, syscomments et sysusers.
SELECT
o.name, o.xtype, c.text, u.name, o.crdate
FROM
dbo.sysobjects o
INNER JOIN dbo.syscomments c
ON c.id = o.id
INNER JOIN dbo.sysusers u
ON u.uid = c.uid
WHERE
xtype = 'TR'
Quelle est la requête qui permet de savoir quelles colonnes d'une table servent de
clé primaire ?
221
Il existe une procédure stockée pour cela :
EXEC sp_pkeys @table_name='PRODUIT'
Quelle commande permet d'afficher la description d'une table sous SQLServer ?
sp_help 'vente.PRODUIT'
Ou
SELECT
column_name AS champ,
COALESCE(domain_name,
cast(data_type as varchar(128))+ ISNULL(' ' + cast(character_maximum_length
as varchar(10)) ,'')) as type_donnee,
CASE UPPER(IS_NULLABLE)
when 'YES' then ''
when 'NO' then 'Oui'
when Null then ''
else IS_NULLABLE
END as Obligatoire,
'' as description
FROM INFORMATION_SCHEMA.columns
WHERE
table_name = 'PRODUIT'
ORDER BY table_name, ordinal_position
Comment récupérer la valeur par défaut d'un champ d'une table ?
SELECT cdefault
FROM syscolumns
WHERE id = object_id('PRODUIT')
and name = 'LIBART'
Quel est le nombre de lignes de chacune des tables d'une base de données ?
SELECT O.Name AS Table_Name, I.Rows AS Rows_Count
FROM sysobjects O join sysindexes I
ON O.id=I.id
WHERE O.xtype='U'
Comment connaître le nom de la base de données en cours ?
Pour connaître le nom de la base de données en cours, vous pouvez utiliser la fonction
DB_NAME().
SELECT DB_NAME() AS BASE_DE_DONNEES_EN_COURS
Comment afficher la liste des bases de données d'un serveur ?
Vous avez trois méthodes au choix :
1- L'utilisation des vues d'informations de schéma, Exemple :
222
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
Go
2 - La consultation des tables système, bien que non recommandée pour des raisons de
portabilité Exemple :
USE master
Go
SELECT name as BaseDedonneeDuServeur
FROM sysdatabases
Go
3 - L'utilisation de la procédure stockée sp_databases Exemple:
EXEC sp_databases
go
Comment changer le type de données d'une colonne ?
Pour changer le type de données d'une colonne, MS SQL Serveur fournit la clause Alter
Column. Cet exemple ferait l'affaire:
ALTER TABLE MyTable
ALTER COLUMN MyColumn NVARCHAR(20) NOT NULL
Vous pouvez également procéder comme ceci :
• Démarrer une transaction sérialisée;
• Créer une nouvelle table avec le nouveau type de données tel que souhaitée;
• Importer les données de l'ancienne table vers la nouvelle;
• Supprimer l'ancienne table;
• Renommer la nouvelle table avec l'ancien nom;
Exemple : Supposons que nous ayant une table T_Person dont la définition est :
CREATE TABLE Tmp_T_PERSONNE
(
PER_ID int NOT NULL,
PER_NOM varchar(50) NOT NULL,
PER_PRENOM varchar(50) NULL,
PER_NE_LE smalldatetime NOT NULL,
) ON [PRIMARY]
GO
--Et que nous voulons changer le type Per_Nom du type varchar(50) au type
varchar(100)
--Nous aurons :
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--Créer une table temporaire ayant même structure que la première
CREATE TABLE Tmp_T_PERSONNE
(
PER_ID int NOT NULL,
PER_NOM varchar(100) NOT NULL,
PER_PRENOM varchar(50) NULL,
223
PER_NE_LE smalldatetime NOT NULL,
) ON [PRIMARY]
GO
-- Peupler la table
IF EXISTS(SELECT * FROM T_PERSONNE)
EXEC('INSERT INTO Tmp_T_PERSONNE (PER_ID,PER_NOM, PER_PRENOM, PER_NE_LE,
PAY_ID, PER_NE_A)
SELECT PER_ID, PER_NOM, PER_PRENOM, PER_NE_LE FROM T_PERSONNE TABLOCKX')
GO
--Supprimer la table
DROP TABLE dbo.T_PERSONNE
GO
--Renommer la nouvelle table avec l'ancien nom
EXECUTE sp_rename N'Tmp_T_PERSONNE', N'T_PERSONNE', 'OBJECT'
GO
COMMIT
Comment renommer une base de données ?
Pour renommer une base de données, MS SQL Server fournit la procédure stockée
sp_renamedb. Exemple :
EXEC sp_renamedb('Test','UneBaseTest')
Vous pouvez également créer une nouvelle base de données, importer les données par DTS
de l'ancienne base de données vers la nouvelle, puis supprimer l'ancienne base de données.
Comment visualiser le code T-SQL d’une procédure stockée ?
SELECT text
FROM dbo.syscomments, dbo.sysobjects
WHERE syscomments.id = sysobjects.id
And sysobjects.xtype = 'P'
AND sysobjects.name='MaProcédure'
Mais bien plus simplement, et avec les bons retour-chariots :
sp_helptext 'MaProcédure'
Comment lister les contraintes de clés primaires et étrangères des tables d'une base
de données ?
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'matable'
Comment trouver la liste des tables dont dépend la vue ?
SELECT DISTINCT NECESSAIRE.NAME
FROM SYSOBJECTS AS NECESSAIRE
INNER JOIN SYSDEPENDS AS DEPENDENCES
ON NECESSAIRE.ID = DEPENDENCES.depid
INNER JOIN SYSOBJECTS AS DEPENDANTE
ON DEPENDENCES.id = DEPENDANTE.id
WHERE DEPENDANTE.name='NOMDELAVUE'
224
Comment comparer 2 tables ?
SELECT s1.name, s1.type, s2.name, s2.type
FROM syscolumns s1, syscolumns s2
WHERE s1.id = object_id('MaTable1')
and s2.id = object_id('MaTable2')
and s1.name=s2.name
and s1.type<>s2.type
Comment trouver une table à travers toutes les bases ?
Voici une procédure permettant de rechercher toutes les bases contenant une table de nom
@SCH.@TAB :
DECLARE @SCH NVARCHAR(128), @TAB NVARCHAR(128);
SELECT @SCH = '???' , @TAB = '???';
DECLARE @SQL NVARCHAR(max)
SET @SQL = '';
SELECT @SQL = @SQL + 'SELECT * FROM '
+ name + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' +
COALESCE(@SCH, 'dbo') + ''' AND TABLE_NAME = ''' + @TAB +''';'
FROM sys.databases;
EXEC (@SQL);
Auditer le taux d'occupation de vos disques de manière automatique ?
Voici un ensemble de codes SQL utilisant des procédures système et l'agent SQL pour scruter
le taux d'occupation des disques et remonter une alerte en cas de dépassement.
Création des objets dans la base de données MSDB
Création des tables de suivi de l'évolution de l'espace disque et de leur taux d'occupation.
Notez l'utilisation du schéma S_SYS dans msdb :
USE msdb;
GO
CREATE SCHEMA S_SYS
CREATE TABLE T_A_DISK_DSK
( DSK_ID INT NOT NULL PRIMARY KEY,
DSK_UNIT CHAR(1) NOT NULL UNIQUE CHECK (DSK_UNIT COLLATE
French_CI_AS BETWEEN 'C' AND 'Z'),
DSK_ALERT_PC FLOAT NOT NULL DEFAULT 30.0 CHECK (DSK_ALERT_PC BETWEEN
0.0 AND 100.0))
CREATE TABLE T_A_TRACE_SPACE_DISK_TSP
( TSP_ID INT NOT NULL PRIMARY KEY,
DSK_UNIT CHAR(1) NOT NULL FOREIGN KEY REFERENCES T_A_DISK_DSK
(DSK_UNIT),
TSP_DATETIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
TSP_SIZE_MO INT NOT NULL,
TSP_USED_MO INT NOT NULL);
GO
CREATE INDEX X_TSP_DTM ON S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_DATETIME,
DSK_UNIT);
GO
225
Création de la procédure de capture des données d'espace disque :
CREATE PROCEDURE S_SYS.P_AUDIT_SPACE_DISK
AS
SET NOCOUNT ON;
DECLARE @HDL int,
@FSO int,
@HD char(1),
@DRV int,
@SZ varchar(20),
@MB bigint ;
SET @MB = 1048576;
CREATE TABLE #HD (HD_UNIT char(1) PRIMARY KEY,
HD_FREESPACE int NULL,
HD_SIZE int NULL);
INSERT INTO #HD (HD_UNIT, HD_FREESPACE)
EXEC master.dbo.xp_fixeddrives;
DELETE FROM #HD
WHERE HD_UNIT NOT IN (SELECT DSK_UNIT
FROM S_SYS.T_A_DISK_DSK);
EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject',@FSO OUT;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
DECLARE C CURSOR LOCAL FAST_FORWARD
FOR SELECT HD_UNIT
FROM #HD;
OPEN C;
FETCH NEXT FROM C INTO @HD;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @HDL = sp_OAMethod @FSO, 'GetDrive', @DRV OUT, @HD
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
EXEC @HDL = sp_OAGetProperty @DRV, 'TotalSize', @SZ OUT
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @DRV;
UPDATE #HD
SET HD_SIZE = CAST(@SZ AS FLOAT) / @MB
WHERE HD_UNIT = @HD;
FETCH NEXT FROM C INTO @HD;
END
CLOSE C;
DEALLOCATE C;
EXEC @HDL=sp_OADestroy @FSO;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
226
INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_UNIT, TSP_SIZE_MO,
TSP_USED_MO)
SELECT HD_UNIT, HD_SIZE, HD_SIZE
- HD_FREESPACE
FROM #HD
DROP TABLE #HD;
RETURN;
GO
Mise en place dans l'agent SQL Server serveur d'une routine journalière de scrutation à 5h du
matin
CREATE PROCEDURE S_SYS.P_AUDIT_SPACE_DISK
AS
SET NOCOUNT ON;
DECLARE @HDL int,
@FSO int,
@HD char(1),
@DRV int,
@SZ varchar(20),
@MB bigint ;
SET @MB = 1048576;
CREATE TABLE #HD (HD_UNIT char(1) PRIMARY KEY,
HD_FREESPACE int NULL,
HD_SIZE int NULL);
INSERT INTO #HD (HD_UNIT, HD_FREESPACE)
EXEC master.dbo.xp_fixeddrives;
DELETE FROM #HD
WHERE HD_UNIT NOT IN (SELECT DSK_UNIT
FROM S_SYS.T_A_DISK_DSK);
EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject',@FSO OUT;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
DECLARE C CURSOR LOCAL FAST_FORWARD
FOR SELECT HD_UNIT
FROM #HD;
OPEN C;
FETCH NEXT FROM C INTO @HD;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @HDL = sp_OAMethod @FSO, 'GetDrive', @DRV OUT, @HD
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
EXEC @HDL = sp_OAGetProperty @DRV, 'TotalSize', @SZ OUT
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @DRV;
227
UPDATE #HD
SET HD_SIZE = CAST(@SZ AS FLOAT) / @MB
WHERE HD_UNIT = @HD;
FETCH NEXT FROM C INTO @HD;
END
CLOSE C;
DEALLOCATE C;
EXEC @HDL=sp_OADestroy @FSO;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_UNIT, TSP_SIZE_MO,
TSP_USED_MO)
SELECT HD_UNIT, HD_SIZE, HD_SIZE
- HD_FREESPACE
FROM #HD
DROP TABLE #HD;
RETURN;
GO
Vous devez remplacer "ServerSQL[\instance]" par le nom de votre serveur et SA par le
compte de connexion sous lequel cette routine doit tourner.
Comment importer ou exporter un diagramme ?
Les informations sur les diagrammes sont stockées dans la table dtproperties dans chaque
base de données. Voici une procédure pour transférer les diagrammes d'une base de données
vers une autre :
1 - Faire un clic droit sur la base qui contient le schéma a transféré, cliquez sur « Toutes les
