Could you please help me out here.
I have multiple databases and each of the databases have the same tables and stored procedures.
I want to be able to compare the stored procedures and see if there were any changes made to one stored procedure in one database, that has not been replicated across the remaining databases.
I also want to be able to fine out if there were changes made to any tables (structure or new tables added in one and not in the rest). I do not need to know if the data within a table has been updated or if one table has more rows than the other.
Best Answer
Don't be tempted to re-invent the wheel. This problem has been solved many times before you came across it.
Take a look at schema comparison tools, such as those available in SQL Server Data Tools (SSDT), and commercial options like Red-Gate SQL Compare.
Going forward you can also use DDL Triggers to log certain DDL events, helping you catch outlying changes that only happened in one place. If you want to do more reading, I have a few tips about this handy feature here, here and here.