6
novembre
2011
SQL SERVER – Métadonnées niveau database
novembre
2011
Un article de zinzineti
Les métadonnées permettent de décrire le rôle et la signification des objets d’une base de données. Les métadonnées représentent en quelque sorte le dictionnaire des objets de la base. Voici une procédure stockée qui permet à la fois de visualiser, d’ajouter, de mettre à jour et de supprimer des métadonnées au niveau database. Les métadonnées peuvent être posées au niveau : base – schema – table – colonne – index – filegroup – datafile – ….
Voici une procédure stockée qui permet à la fois de visualiser, d’ajouter, de mettre à jour et de supprimer des métadonnées au niveau base.
/*===================================================================
-- Description : SELECT - ADD - UPDATE - DROP de métadonnées niveau Database
-- Exemple d'utilisation :
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='ADD',@TITRE='Description', @COMMENTAIRE='Base de données exemple'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='UPDATE',@TITRE='Description',@COMMENTAIRE='Base de données exemple With UPDATE....'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='DROP',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
-- Auteur : Etienne ZINZINDOHOUE
=================================================================== */
USE master
GO
CREATE PROCEDURE sp_comments_on_database
@DB SYSNAME=NULL, @OPERATION CHAR(6)=NULL,@TITRE VARCHAR(50)=NULL,@COMMENTAIRE VARCHAR(7500)=NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Comments VARCHAR(7500);
DECLARE @SQLSELECT VARCHAR(max);
SET @Comments = convert(VARCHAR,GETDATE(),120) + ' : ' + @COMMENTAIRE
SET @OPERATION = RTRIM(RTRIM(@OPERATION));
--> s'il n'y a aucun paramètre on affiche la liste des métadonnées de la base
IF (@DB IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL AND @COMMENTAIRE IS NULL)
BEGIN
SELECT class_desc,name,value FROM sys.extended_properties WHERE class = 0
END
--> s'il n'y a aucun paramètre on affiche la liste des métadonnées de la base
ELSE IF (@DB IS NOT NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' SELECT class_desc,name,value FROM sys.extended_properties WHERE class = 0'
EXEC (@SQLSELECT)
END
--> Si @OPERATION = ADD
ELSE IF (@DB IS NOT NULL AND @OPERATION ='ADD'AND @TITRE IS NOT NULL AND @COMMENTAIRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_addextendedproperty @name = '''+@TITRE + ''', @value = ''' + @Comments +''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si @OPERATION = UPDATE
ELSE IF (@DB IS NOT NULL AND @OPERATION ='UPDATE'AND @TITRE IS NOT NULL AND @COMMENTAIRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_updateextendedproperty @name = '''+@TITRE + ''', @value = ''' + @Comments +''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si operation = DROP
ELSE IF (@DB IS NOT NULL AND @OPERATION ='DROP'AND @TITRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_dropextendedproperty @name = '''+@TITRE + ''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si @OPERATION = 'SELECT'
ELSE IF (@DB IS NOT NULL AND @OPERATION ='SELECT'AND @TITRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default) WHERE name = '''+ @TITRE +''''
EXEC (@SQLSELECT)
END
ELSE
SELECT 'Cette métadonnée n''existe dans la base ' + @DB
END
GO
--> marquer la procédure comme générique
EXEC sys.sp_MS_marksystemobject 'sp_comments_on_database'
GO
-- Description : SELECT - ADD - UPDATE - DROP de métadonnées niveau Database
-- Exemple d'utilisation :
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='ADD',@TITRE='Description', @COMMENTAIRE='Base de données exemple'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='UPDATE',@TITRE='Description',@COMMENTAIRE='Base de données exemple With UPDATE....'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='DROP',@TITRE='Description'
EXEC dbo.sp_comments_on_database
@DB='AdventureWorks',@OPERATION='SELECT',@TITRE='Description'
-- Auteur : Etienne ZINZINDOHOUE
=================================================================== */
USE master
GO
CREATE PROCEDURE sp_comments_on_database
@DB SYSNAME=NULL, @OPERATION CHAR(6)=NULL,@TITRE VARCHAR(50)=NULL,@COMMENTAIRE VARCHAR(7500)=NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Comments VARCHAR(7500);
DECLARE @SQLSELECT VARCHAR(max);
SET @Comments = convert(VARCHAR,GETDATE(),120) + ' : ' + @COMMENTAIRE
SET @OPERATION = RTRIM(RTRIM(@OPERATION));
--> s'il n'y a aucun paramètre on affiche la liste des métadonnées de la base
IF (@DB IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL AND @COMMENTAIRE IS NULL)
BEGIN
SELECT class_desc,name,value FROM sys.extended_properties WHERE class = 0
END
--> s'il n'y a aucun paramètre on affiche la liste des métadonnées de la base
ELSE IF (@DB IS NOT NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' SELECT class_desc,name,value FROM sys.extended_properties WHERE class = 0'
EXEC (@SQLSELECT)
END
--> Si @OPERATION = ADD
ELSE IF (@DB IS NOT NULL AND @OPERATION ='ADD'AND @TITRE IS NOT NULL AND @COMMENTAIRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_addextendedproperty @name = '''+@TITRE + ''', @value = ''' + @Comments +''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si @OPERATION = UPDATE
ELSE IF (@DB IS NOT NULL AND @OPERATION ='UPDATE'AND @TITRE IS NOT NULL AND @COMMENTAIRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_updateextendedproperty @name = '''+@TITRE + ''', @value = ''' + @Comments +''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si operation = DROP
ELSE IF (@DB IS NOT NULL AND @OPERATION ='DROP'AND @TITRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' EXEC sp_dropextendedproperty @name = '''+@TITRE + ''''
EXEC (@SQLSELECT)
IF (@@ERROR = 0)
SELECT 'Opération reussie ;-)'
ELSE
SELECT 'Echec de l''opération !'
END
--> Si @OPERATION = 'SELECT'
ELSE IF (@DB IS NOT NULL AND @OPERATION ='SELECT'AND @TITRE IS NOT NULL)
BEGIN
SET @SQLSELECT = 'USE ' + @DB + ' SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default) WHERE name = '''+ @TITRE +''''
EXEC (@SQLSELECT)
END
ELSE
SELECT 'Cette métadonnée n''existe dans la base ' + @DB
END
GO
--> marquer la procédure comme générique
EXEC sys.sp_MS_marksystemobject 'sp_comments_on_database'
GO
——————————–
Etienne ZINZINDOHOUE
——————————–