Tester et supprimer une vue SQL Server si elle existe

Comment tester et ensuite supprimer une vue SQL Server, si elle existe dans la base de données ?

Voici comment tester l’existence d’une une vue SQL Server avant de la supprimer. Utiliser ce script comme un exemple pour tester l’existence de la vue avant de la supprimer. L’objectif est de ne pas générer d’erreur pendant l’exécution d’un script de déploiement par exemple. Et ainsi de ne pas exécuter un script sur un objet inexistant, ou renommé par exemple.

1. Supprimer une vue SQL Server si elle existe

En effet, supprimer une vue inexistante retourne cette erreur dans SQL Server Management Studio (SSMS). 

Cannot drop the view ‘dbo.V_VENTES’, because it does not exist or you do not have permission.

Ou le même message d’erreur en Français :

Msg 3701 : La vue n’existe pas ou vous n’avez pas les droits.

Voici la solution, utiliser la fonction SQL Server IF EXISTS pour tester l’existence de la vue avant de la supprimer, comme dans cet exemple.

IF EXISTS ( 
  SELECT 1 
  FROM sys.objects 
  WHERE object_id = object_id(N'[dbo].[V_VENTES]') 
  AND type in (N'V') 
) 
BEGIN DROP VIEW [dbo].[V_VENTES]; 
END;

2. Supprimer une vue à partir de la version 2016

Script plus court avec une seule ligne de code, la fonction DROP VIEW IF EXISTS, qui est disponible depuis la version SQL Server 2016.

IF EXISTS DROP VIEW [dbo].[V_VENTES];

3. Supprimer toutes les vues d’une base SQL Server

Enfin, pour cette troisième partie sur la suppression des vues, voici comment créer un script dynamique avec une boucle pour filtrer et supprimer toutes les vues avec un préfixe ou un suffixe particulier. Ce code en plusieurs parties permet d’automatiser ces étapes :

1. Créer les variables
2. Instancier les variables
3. Créer un curseur pour sélectionner le nom des vues avec un filtre sur le nom
4. Parcourir le curseur
5. Pour chaque ligne du curseur, construire et afficher une requête pour supprimer la vue
6. Fermer et supprimer la référence du curseur.

DECLARE
  @View_Name AS NVARCHAR(50),
  @Drop_SQL_Query AS NVARCHAR(100),
  @Drop_SQL_Query_Full AS NVARCHAR(200);

SET @Drop_SQL_Query = 'IF EXISTS DROP VIEW ';

DECLARE View_Name_Cursor CURSOR   FOR 
  SELECT [Name] 
  FROM SYS.VIEWS
  WHERE [Name] LIKE '%_SALES_%'
  ORDER BY [Name];

OPEN View_Name_Cursor;
FETCH NEXT FROM View_Name_Cursor into @View_Name;

WHILE @@FETCH_STATUS = 0  
BEGIN 
  SET @Drop_SQL_Query_Full = @Drop_SQL_Query + @View_Name;
  PRINT @Drop_SQL_Query_Full;
  --EXEC @Drop_SQL_Query_Full;
  FETCH NEXT FROM View_Name_Cursor into @View_Name;
END

CLOSE View_Name_Cursor;  
DEALLOCATE View_Name_Cursor;  

En effet, ce script permet de supprimer toutes les vues créées dans une base de données. Il utilise un filtre, facilement modifiable pour sélectionner les vues basées sur un suffixe, un préfixe, un filtre comme dans l’exemple ci-dessus, ou encore simplement toutes les vues de la base.

Ce script ne supprime pas les vues, mais génère de façon dynamique les commandes pour supprimer les vues. Pour l’exécuter directement à l’intérieur du code T-SQL, retirer simplement les commentaires à la ligne suivante.

EXEC sp_executesql @Drop_SQL_Query_Full;

Ce sont des requêtes de type Langage de Définition de Donnée (dont l’abréviation est LDD), appelé aussi DDL pour Data Definition Language en Anglais. Cette page plus générale sur les vues expose quelques exemples des principales commandes LDD pour gérer les vues avec des scripts T-SQL.

Pour conclure, ce tutoriel explique Tester et supprimer une vue SQL Server avec les versions avant SQL Server 2016, et les suivantes en une seule ligne. Puis présente un script dynamique pour supprimer toutes les vues d’une base SQL.

Gérer les vues SQL Server