Something I found frustrating when designing databases, were the frequent changes made to tables. Managing the scripts can become quite tricky, especially if you are still in the design phase. This is made worse by code changes or change requests to existing data structures. Sometimes a person gets so caught up in the changes you need to make, that you forget about scripting these table changes.
Looking for something else? Try these links instead:
- Automate Standard Comment Blocks For New SQL Queries
- Azure Databases – Restore Local Database To Azure
- Execute Script Error Resolved In SQL Server Management Studio
- ApexSQL Provides Excellent SQL Tools For Free
- Query Stack Overflow With T-SQL – Give Your Queries A Workout
Here is a nice trick in SQL Server Management Studio to easily generate table change scripts. Never forget to script your changes again.
Easily Generate Table Change Scripts
In SQL Server Management Studio, select the Options sub-menu under Tools.
In the Options dialogue, select the Designers node in the tree view on the left. Here you will find an option ‘Auto generate change scripts’. Ensure that you check this option and click on OK.
Head on over to one of your existing tables and change something (eg. the column data type) from the table designer. When you save the changes, SQL Server Management Studio will prompt you to save the change script. Just take note that if you uncheck the option ‘Automatically generate change script on every save’, then you effectively switch off the option you enabled from the Options screen above. You will need to re-enable this from the options. Also remember that deleting tables from the database is also not covered by change scripts. Probably because this isn’t a table change. 🙂
SQL Server Management Studio can streamline so many manual tasks. It is just up to us to go and discover these hidden gems that make a developer’s life so much easier. If you have any tips to share, let us know in the comments.
