SQL – Etienne ZINZINDOHOUE

/*===================================================================
-- Description : SELECT - ADD - UPDATE - DROP de métadonnées niveau colonnne
 
-- Exemple d'utilisation :  
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='ADD',@TITRE='Description', @COMMENTAIRE='Identifiant des contacts'
 
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='SELECT',@TITRE='Description'
 
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='UPDATE',@TITRE='Description', @COMMENTAIRE='Identifiant des contacts With Update'
 
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='SELECT',@TITRE='Description'
 
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='DROP',@TITRE='Description'
 
EXEC dbo.sp_comments_on_column  
@DB='AdventureWorks',@SCHEMA='Person',@TABLE='Contact',@COLUMN='ContactID',@OPERATION='SELECT',@TITRE='Description'
 
-- Auteur : Etienne ZINZINDOHOUE  
=================================================================== */
USE master
GO
CREATE PROCEDURE sp_comments_on_column  
@DB SYSNAME=NULL,@SCHEMA SYSNAME=NULL, @TABLE SYSNAME=NULL, @COLUMN 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 colonnes des tables de la base courante
IF (@DB IS NULL AND @SCHEMA IS NULL AND @TABLE IS NULL AND @COLUMN IS NULL AND @OPERATION IS NULL AND @TITRE IS NULL)
BEGIN
SELECT t.name [TableName], c.name [Column] , value  
FROM sys.extended_properties e
INNER JOIN sys.tables t ON e.major_id = t.object_id  
INNER JOIN sys.columns c ON e.major_id = c.object_id AND e.minor_id = c.column_id
END
--> si la base est indiquée alors on affiche la liste des métadonnées de toutes les colonnes des tables de la base
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NULL AND @TABLE IS NULL AND @OPERATION IS NULL)
BEGIN
SET @SQL = 'USE ' + @DB +  
' SELECT t.name [TableName], c.name [Column],e.name [Titre], value  
FROM sys.extended_properties e
INNER JOIN sys.tables t ON e.major_id = t.object_id  
INNER JOIN sys.columns c ON e.major_id = c.object_id AND e.minor_id = c.column_id '
EXEC (@SQL)
END
--> si la base et le schema sont indiqués alors on affiche la liste des métadonnées correspondantes
ELSE IF(@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NULL AND @COLUMN IS NULL AND @OPERATION IS NULL)
BEGIN
SET @SQL = 'USE ' + @DB +  
' SELECT t.name [TableName], c.name [Column],e.name [Titre], value  
FROM sys.extended_properties e
INNER JOIN sys.tables t ON e.major_id = t.object_id  
INNER JOIN sys.columns c ON e.major_id = c.object_id AND e.minor_id = c.column_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id  
WHERE s.name =''' + @SCHEMA + ''' '  
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 @COLUMN IS NULL AND (@OPERATION IS NULL OR @OPERATION ='SELECT'))
BEGIN
SET @SQL = 'USE ' + @DB +  
' SELECT t.name [TableName], c.name [Column],e.name [Titre],value  
FROM sys.extended_properties e
INNER JOIN sys.tables t ON e.major_id = t.object_id  
INNER JOIN sys.columns c ON e.major_id = c.object_id AND e.minor_id = c.column_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id  
WHERE s.name =''' + @SCHEMA + ''' AND t.name = ''' + @TABLE +''' '  
EXECUTE (@SQL)
END
--> si la base,le schema,la table et la colonne 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 @COLUMN IS NOT NULL AND (@OPERATION IS NULL OR @OPERATION ='SELECT'))
BEGIN
SET @SQL = 'USE ' + @DB +  
' SELECT t.name [TableName], c.name [Column],e.name [Titre], value  
FROM sys.extended_properties e
INNER JOIN sys.tables t ON e.major_id = t.object_id  
INNER JOIN sys.columns c ON e.major_id = c.object_id AND e.minor_id = c.column_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id  
WHERE s.name =''' + @SCHEMA + ''' AND t.name = ''' + @TABLE +''' AND c.name = '''+ @COLUMN + ''' '  
EXECUTE (@SQL)
END
--> Si @OPERATION = ADD
ELSE IF (@DB IS NOT NULL AND @SCHEMA IS NOT NULL AND @TABLE IS NOT NULL AND @COLUMN 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 + ''',
@level2type = N''COLUMN'',
@level2name = ''' + @COLUMN + ''' '
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 @COLUMN 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 + ''',
@level2type = N''COLUMN'',
@level2name = ''' + @COLUMN + ''' '
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 @COLUMN 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 + ''',
@level2type = N''COLUMN'',
@level2name = ''' + @COLUMN + ''' '
--PRINT(@SQL)
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_column'