Par défaut, les vues créées sous SQL Server sont uniquement des alias, c’est-à-dire que le code qu’elles contiennent (les tables et colonnes utilisées, …) est évalué à chaque exécution. Mais avec ce paramétrage par défaut, il est possible que les tables sous-jacentes évoluent et que l’on obtienne une vue qui, bien que définie, ne peut plus être utilisée.

Voici un petit papier au sujet des vues, et plus particulièrement autour d’une méthode permettant de détecter ces vues « fantômes ».

Tout d’abord, prenons l’exemple d’une petite vue assez simple. Nous nous appuierons sur la base AdventureWorks2012 disponible ici.

CREATE VIEW [dbo].[FullName_Sales]
AS
SELECT p.[FirstName] + N' ' + p.[LastName] as NomComplet,  p.AdditionalContactInfo
FROM [Person].[Person] p
JOIN [Sales].[SalesPerson] s on s.BusinessEntityID=p.BusinessEntityID
GO

Cette vue nous retourne le nom complet des vendeurs et des informations de contact complémentaires. Lorsqu’on l’utilise et que l’on regarde le plan d’exécution, on constate bien que le moteur utilise en fait les tables physiques. En effet, la vue n’est pas matérialisée (il n’y a pas d’index défini sur cette vue), et donc elle n’a qu’un rôle d’alias.

Mais si l’une des tables évolue, la requête n’est plus possible. Par exemple, modifions un peu la table Person. Par exemple, remplaçons le nom de la colonne AdditionalContactInfo par AdditionalContactInfos.

ALTER TABLE [Person].[Person]
	ADD AdditionalContactInfos XML;
GO
UPDATE [Person].[Person] SET AdditionalContactInfos=AdditionalContactInfo;
GO
DROP INDEX PXML_Person_AddContact ON [Person].[Person];
GO
ALTER TABLE [Person].[Person]
	DROP COLUMN AdditionalContactInfo;

Et maintenant, cherchons à réutiliser la vue que nous avions précédemment définie.

Il n’est plus possible de trouver la colonne d’après le nom défini dans la vue, et donc la vue ne fonctionne plus.

Nous constatons donc qu’il est très facile, lorsque l’on fait vivre un modèle de données, d’oublier derrière soi des vues. Si ces vues ne sont plus utilisées, cela ne pose pas de problème particulier, et tout continue à fonctionner correctement.

Une solution pour éviter ce genre de désagrément est de lier les vues au schéma de données (mot-clé SCHEMABINDING dans la définition de la vue). Mais dans la pratique, on oublie très souvent de mettre en place cette sécurité, et on se retrouve donc au fil des évolutions de la base, avec un certain nombre de « fantômes » de vues.

Afin de tester une vue pour savoir si elle est toujours fonctionnelle, on a la possibilité de tout simplement la tester (via une requête SELECT).

Une autre solution consiste à utiliser la procédure stockée système sp_refreshview. Celle-ci permet de vérifier les métadonnées associées à la vue sans pour autant la lancer. Ainsi, SQL Server Management Studio (SSMS) présentera par exemple le bon type de données pour les colonnes de la vue si le type des colonnes sous-jacentes a évolué.

L’utilisation de cette procédure provoque une erreur s’il n’est plus possible de déterminer les métadonnées associées à la vue, c’est-à-dire si la vue n’est plus utilisable.

Il suffit donc d’englober l’utilisation de cette procédure dans une boucle pour pouvoir tester d’un coup l’ensemble des vues d’une base.

-- Vérification des vues utilisateur
-- Jean-Nicolas BERGER - http://www.sqlserver.fr

SET NOCOUNT ON
DECLARE @Vue AS nvarchar(max)
DECLARE @NbOk int, @NbKo int
DECLARE @MessageErreur nvarchar(max)
DECLARE @ListeErreurs table (Vue nvarchar(max), [Message] nvarchar(max))

DECLARE liste INSENSITIVE CURSOR 
FOR SELECT QUOTENAME(s.name)+'.'+QUOTENAME(v.name)
     FROM sys.views v
	 join sys.schemas s on s.schema_id=v.schema_id
WHERE OBJECTPROPERTY(v.object_id, 'IsSchemaBound')=0

OPEN liste
FETCH NEXT FROM liste into @Vue
SET @NbKo=0
SET @NbOk=0

WHILE (@@FETCH_STATUS <> -1)
BEGIN
    BEGIN TRY
        EXEC sp_refreshview @Vue
		select @NbOk=@NbOk+1
    END TRY
    BEGIN CATCH
		IF XACT_STATE() = -1 ROLLBACK
		SET @MessageErreur=ERROR_MESSAGE()
        insert into @ListeErreurs(Vue,[Message]) values (@Vue,@MessageErreur)
		select @NbKo=@NbKo+1
    END CATCH
    FETCH NEXT FROM liste INTO @Vue
END
CLOSE liste
DEALLOCATE liste

print 'Nb de vue correctes : ' + convert(nvarchar(max),@NbOk)
print 'Nb de vue incorrectes : ' + convert(nvarchar(max),@NbKo)
if @NbKo>0
	select Vue,[Message] from @ListeErreurs

On constate que la modification de nom de colonne n’a pas seulement impacté la vue de notre test, mais aussi d’autres.

Lancer cette requête sur vos bases de production risque fort de vous surprendre en vous rappelant du code que vous aviez oublié depuis quelques temps …