DB Developer : Track Developmental Changes Of Your Database Tables

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

 

Advertisements

PHISSUG and a friend!!!

March 2008, PHISSUG (Philippine SQL Server Users Group) officers Vince, Mike and Dennis and yours truly trekked Taal Volcano for some landscape photography binge. We left Manila before 4am with this handsome puppy! The story was that, as I left home, I noticed this pup was following me as I walked from home to where the guys would fetch me. It was basically a couple hundred meters walk from the house. The pup would follow me as I noticed 2 cats were harassing the poor puppy. I dunno who the owner was as it was still around 4:00am and every house I passed by were still close and their lights off. Got pity on the pup and shoo’d away the cats and decided to let him follow me. Thought he would give up chasing me but he would to no end until I got to see the PHISSUG guys.

We all decided to bring him along. I took the responsibility of carrying him. And man, he was heavy for a little pup. Despite having a camera, a backpack and a tripod, I carried him. Once in the vehicle, the pup would cry and would only stop if I attend to him. Instant bonding!!! Me, now a surogate parent to this poor fella.

We reached the foot of the volcano just after daybreak and we decided to trek up to the crater. By this time, everybody we met took notice of this puppy like a celebrity and everytime they asked, we would tell them we only picked him up from nowhere.

As we had our ascend to the crater, I let the pup just walk and run with us. He would gamely follow us and would start to cry if he senses that I/we were gone (hahahha). Sometimes he would attempt to go back down. Sometimes he would chase and go with a horse that crossed our way. He did the walking and running until his tongue is almost on the ground (dont say animal cruelty, he seem to enjoy our company).

From this point onwards, I decided to carry this stocky little fella. I got him comfortable inside a Microsoft bag. All the way up to the crater, and he would just sleep inside the Microsoft bag. If he gets too heavy, I rest and he would just sleep beside me.

As soon as we reached the top, we let him loose. He would snoop around and he would sleep, watch the tourists around, eat, drink, etc. Seems he was happy with his life while my foot, my shoulder and my back were aching becuase of you know who.

We had our time photographing the beautiful volcano until it was time to go home. One thing wasn’t settled. I cannot bring him home. Pups were not allowed in my place. Dennis, Vince and Mike says … uh uh … negative!

Then we all decided to give him to a man who got to like him and found out he was assigned to provide security in the area.

Problem solved. But I felt sad to leave a new found friend behind. But the pup looks contented to be there and he looks happy and he seem to want to stay behind.

It was time to go, we bade goodbye and left.

I hope to go up there again with the PHISSUG guys and hopefully get to see how this pup had become. He must be a handsome dog now. The PHISSUG guys should reunite with an old friend someday!

This pup, the PHISSUG guys named him TAAL!

**************************************************
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