This is my own contribution to the T-SQL Tuesday I am hosting – on managing database code.
I am from the older generation – where the farthest we went with managing code was use VSS/TFS to get the latest version and deploy it. In some cases, I’ve managed it. If we wanted to search for existence of an object or its usage, we simply searched the database – using some custom tools/dmvs or 3rd party tools like RedGate’s SQL Search. History of who changed what – well that came from their tickets or if it was code – from code comment history. With the advent of GIT, all that changed. I did not adapt to GIT very quickly. I went, kicking and screaming. I hated the clunky interface. I hated not knowing what the command actually did, but still had to use it. I did not see the point of learning what I thought to be over engineered version control. But over time, I’ve learned its value. I can find almost any code that has been committed. I can see who changed what, and when, very easily. I can search for code usage and objects, really quickly. Granted, the interface is still clunky and merge conflicts aren’t fun when they happen, but the advantages override all that, significantly. There is one place where we can easily see all the code related to a database. We can evaluate how complex it is and even use it for legal reasons as code base of the company.
As of now, I use GIT for source control extensively. At work all our database objects are scripted out and have their repo. When we make a change, the new objects are auto scripted with the changes.
I am a fan of SMO, for this purpose and have scripts I’ve developed myself. Granted, SMO only works for SQL Server. I am learning more on similar tools for Postgres. I am often frustrated that Redshift has none. I strongly advocate using GIT and putting all your database objects into a repo using whatever means you can get. (Or develop your own means). It is worth a lot more than you think.