Détection des objets SQL Invalides

Après modifications de structures de tables, le script suivant permettra d’identifier les impacts sur les objets devenus invalides.

DECLARE @Name NVARCHAR(1000);

DECLARE ObjectCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
	FROM sys.objects AS o
	WHERE type IN('FN', 'P', 'TF', 'TR', 'V ')
		  AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0;

OPEN ObjectCursor;

FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		EXEC sp_refreshsqlmodule @Name;
	END TRY
	BEGIN CATCH
		PRINT @Name + ' : ' + ERROR_MESSAGE();
	END CATCH;

	FETCH NEXT FROM ObjectCursor INTO @Name;
END;

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;