As a database developer, you need to track changes to your tables the moment it has been created during development and testing stages, up to when you have deployed and done some modifications and finally when you deprecate it. Without the benefit of expensive tools that does the same thing for you, you can keep track structural changes to a database table in Microsoft SQL Server using just SSMS. For newbies out there, this has nothing to do with tracking a database table against data/content changes. Let us save that subject for another article.
One of the first things you need to do after a fresh install of SSMS is to enable the Auto generate change script option in one of SSMS’ Designers. To enable it in SSMS, you can go to Tools > Options … > Designers > Table and Database Designers and check the option as shown below.
After you have enabled the option, SSMS will prompt you with a filesave dialog containing scripts of the changes. You may want to test the feature before you can trust it.
Here, we can try to simulate some real word scenarios and see if this feature holds up. Now Using SSMS table designer, let us do the following:
1. Create TABLE1, with col1 and col2 in it. Save the change script to “TABLE1-BUILD1.SQL”
You can then send this script to a fellow developer or anyone who needs the same table as you do or probably start filling the table with data.
2. After sometime, you may want to add col3 on TABLE1 to introduce some features that you have forgotten when you first created the table. Meanwhile we can call this modification as part of our BUILD2 and save the change script to “TABLE1-BUILD2.SQL”
Checking out the content of the script will show you that SSMS is clearly aware that your table has some data in it and will do a good job preserving it. Fellow developers, customers or even some instances of the database table you maintain needing an upgrade to BUILD2 can have this script safely to modify various instances of BUILD1 of TABLE1.
3. In some cases, you may want to change a column’s name to a new one or deprecate some features by deleting some columns. Just do it in SSMS, hit save and once again you get a new modification script. Save it to “TABLE1-BUILD3.SQL”
Once again, BUILD2 instances of TABLE1 can be upgraded to BUILD3 using script “TABLE1-BUILD3.SQL”.
You can repeat the cycle until you have BUILDX of TABLE1. To deprecate TABLE1 itself though, you need to script out a DROP TABLE yourself as this tool does not automagically generates DROP TABLE scripts.
Furthermore, you can create new instances of TABLE1 to whatever version or build you want it to be. To create a new BUILD3 version TABLE1, just run scripts TABLE1-BUILD1, TABLE1-BUILD2 and TABLE1-BUILD3 sequentially.
CAUTION: If you happen to update the table outside of this designer, it won’t be tracked. You still need to manually script and incorporate the changes in a file.
Hope this helps!
**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com
totogamboa.wordpress.com 