As a DBA you want to know when database schema has been changed:
a table has been altered,
a new table has been created or
a table has been deleted
The usp_dba_schema_ver_cntrl stored procedure will send e-mail if these schema
changes occurred. The best way to run it is through a scheduled job that will run regularly
once every night. Please replace recipients string with the semicolon-separated list
of the recipients of the mail.
This stored procedure checks the schema_ver and crdate columns in the system table
sysobjects. According to the BOL, the schema_ver value gets incremented every time the schema
for a table changes. The crdate column stores the date when a table been created. Checking
the crdate value prevents anyone from dropping and recreating a table with the same name but
different schema.
Download usp_dba_schema_ver_cntrl