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;