How to check if Alter view/stored proc script has run successfully

For the database deployments, I call all the db schema changes in a batch file and run them on the target database.

Sometimes we have scripts for altering the views or stored procs. I'm wondering what is the best way to control whether the view/sp has been altered successfully or not and report it via a message in the output log file.

For example below is the pseudo code of what I'd like to implement:

IF 
(
ALTER VIEW vw_abc
.
.
.
.
) Failed THEN 

PRINT 'ALTER view vw_abc Failed'.
ELSE
PRINT 'ALTER view vw_abc Completed Successfully'
END

The output message will be recorded in the script log file which is generated by the batch file that has run the script.

If there any error code that we can check to do that? Any idea?

Thanks.

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started.

SELECT SYSDATETIME() AS ScriptStart
INTO #scriptstart;
GO
ALTER VIEW ...
GO
ALTER PROC ...
GO
SELECT name
FROM sys.objects
WHERE modify_date > (SELECT ScriptStart FROM #scriptstart);

This will list all the objects that have changed since your script started. You could join to sys.schemas if you care enough... :)

StackExchange Database Administrators Q#94232, answer score: 3