I know that there are tools out there which allow for schema comparison (SQL Compare for example). What I'm looking for is a bit more low-level.
I'd like a script that I can run on a daily basis, which will just alert me to differences in schema between two databases. I'd imagine that there are probably system-level tables which will provide me with this information. Can someone point me towards which tables these would be, so I can create the script? Or alternatively if there are scripts already available, to point me to those?
I'd imagine that sys.tables / sys.columns would be the one I would look at for table comparison, and sys.procedures / sys.sql_modules for the stored procedures. Are there any others I should be looking at? Which ones store functions?