SQL – Etienne ZINZINDOHOUE

/*===================================================================
-- Description : SELECT - ADD - UPDATE - DROP de métadonnées niveau table
 
-- Exemple d'utilisation :  
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='ADD',@TITRE='Description', @COMMENTAIRE='Table des contacts'
 
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='SELECT',@TITRE='Description'
 
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='UPDATE',@TITRE='Description',@COMMENTAIRE='Table des contacts With Update'
 
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='SELECT',@TITRE='Description'
 
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='DROP',@TITRE='Description'
 
EXEC dbo.sp_comments_on_table @DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@OPERATION='SELECT',@TITRE='Description'
 
-- Auteur : Etienne ZINZINDOHOUE  
=================================================================== */
USE master
GO
CREATE PROCEDURE sp_comments_on_table  
@DB SYSNAME=NULL,@SCHEMA SYSNAME=NULL, @TABLE SYSNAME=NULL, @OPERATION CHAR(6)=NULL,@TITRE VARCHAR(50)=NULL,@COMMENTAIRE VARCHAR(7500)=NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Comments VARCHAR(7500);
DECLARE @SQL 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 toutes les tables du schema dbo de la base courante
IF (@DB IS NULL AND @SCHEMA IS NULL AND @TABLE IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
EXEC (@SQL)
END
--> si la base est indiquée alors on affiche la liste des métadonnées de toutes les tables du schema dbo
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NULL AND @TABLE IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SET @SQL = 'USE ' + @DB + '
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, ''schema'', ''dbo'', ''table'', default, NULL, NULL) '
EXEC (@SQL)
END
--> si la base et le schema sont indiqués alors on affiche la liste des métadonnées de toutes les tables correspondantes
ELSE IF(@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' SELECT objtype, objname, name, value FROM fn_listextendedproperty(NULL, ''schema'', ''' + @SCHEMA + ''', ' +'''table''' + ','+'default, NULL, NULL) '  
EXECUTE (@SQL)
END
--> si la base,le schema et la table sont indiqués alors on affiche les métadonnées correspondantes
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND (@OPERATION IS NULL OR @OPERATION ='SELECT' )AND @TITRE IS NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' SELECT objtype, objname, name, value FROM fn_listextendedproperty(NULL, ''schema'', ''' + @SCHEMA + ''', ' +'''table''' + ','''+@TABLE +''', NULL, NULL)'
EXECUTE (@SQL)
END
--> si la base,le schema,la table et le titre de la métadonnée sont indiqués alors on affiche la métadonnée correspondante
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND (@OPERATION IS NULL OR @OPERATION ='SELECT' )AND @TITRE IS NOT NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' SELECT objtype, objname, name, value FROM fn_listextendedproperty(NULL, ''schema'', ''' + @SCHEMA + ''', ' +'''table''' + ','''+@TABLE +''', NULL, NULL) WHERE name = ''' + @TITRE + ''''
EXECUTE (@SQL)
END
--> Si @OPERATION = ADD
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND @OPERATION ='ADD' AND @TITRE IS NOT NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' EXEC sp_addextendedproperty  
@name = ''' + @TITRE + ''',  
@value = ''' + @Comments + ''',
@level0type = N''SCHEMA'',  
@level0name = ''' + @SCHEMA + ''',
@level1type = N''TABLE'',  
@level1name = ''' + @TABLE + ''' '
EXEC (@SQL)
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 @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND @OPERATION ='UPDATE' AND @TITRE IS NOT NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' EXEC sp_updateextendedproperty  
@name = ''' + @TITRE + ''',  
@value = ''' + @Comments + ''',
@level0type = N''SCHEMA'',  
@level0name = ''' + @SCHEMA + ''',
@level1type = N''TABLE'',  
@level1name = ''' + @TABLE +''''
EXEC (@SQL)  
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 @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND @OPERATION ='DROP' AND @TITRE IS NOT NULL)
BEGIN
SET @SQL = 'USE ' + @DB + ' EXEC sp_dropextendedproperty  
@name = ''' + @TITRE + ''',  
@level0type = N''SCHEMA'',  
@level0name = ''' + @SCHEMA + ''',
@level1type = N''TABLE'',  
@level1name = '''+ @TABLE +''''
EXEC (@SQL)
IF (@@ERROR = 0)
  SELECT 'Opération reussie ;-)'
  ELSE
  SELECT  'Echec de l''opération !'  
END  
ELSE
SELECT  'Cette métadonnée n''existe pas sur la table  '+ @DB + @SCHEMA +'.'+ @TABLE
END
GO
--> marquer la procédure comme générique  
EXEC sys.sp_MS_marksystemobject 'sp_comments_on_table'