I’ve written an SQL database for my company, and it’s been in use for 5+ years. There’s over 100 tables and 300 views, so manually updating things isn’t feasible.

Given that I can always revert development to a backup of the current (live) database, what’s the best way to update these schema and all the objects so that nothing gets broken? Is there some simple answer I’m missing?

For reference, I’m self-taught and I built this thing from scratch in MS Access before we migrated the back end to SQL Server. I only have hands-on experience, but no formal database training, so some of the solutions I’ve tried to Google are going right over my head.

I have been able to update the schema for the tables in my development version, but this just breaks all the views and I haven’t been able to figure out how to solve that problem. I’ve been trying to do a brute-force “find-replace” using Excel and a text editor to set up an “Alter Views” script, but for various reasons that’s not working out. Most of the time, the find-replace gets foiled by the fact that certain table names are too similar, or that the names are sometimes in brackets, like [schema].[tablename] so doing a find-replace for schema.tablename misses them. And then there’s the issue of aliases when a table is called multiple times in a view, so it’s auto-referenced as “schema.tablename_01” then “_02” and so on.

I was hoping a simple find-replace would do the job, but it’s looking more complex than that.