>I'm writing a stored procedure to archive data into archive tables. As a safety mechanism I would like to ensure that the source table and the archive table have the exact same structure (ensuring that a change wasn't made in one and not the other). Anyone know how to compare table structures in a stored procedure and fail the stored procedure if the tables do not match?
If you only want to compare types of the fields and the field names, then you can use INFORMATION_SCHEMA.Columns system view.
For more comprehensive check there are utilities available (some free), such as Structure Compare from Red Gates.
If it's not broken, fix it until it is.
My Blog