Dropping Default Constraints in MSSQL
I came across this today: Working With Default Constraints
Basically, if you script any sort of table changes in MSSQL you will eventually need to use this code. In MSSQL’s infinite wisdom they made default values on NOT NULL columns occasionally have a mysterious constraint. Ordinarily, constraints on fields on tables are easily found in INFORMATION_SCHEMA.TABLE_CONSTRAINTS; but, these particular constraints are hidden for whatever reason.
This makes removing them programmatically very difficult, since they insist on putting a timestamp/guid on the end of each auto-generated default constraint. SO, this useful page on the MSDN provides a way to query the actual name of the constraint given the table name and column name. This, allowing you to remove it before you do something completely crazy like delete the column.
Of course, if your not in to scripting your database migrations then you can just use Management Studio and delete the column.
grampa said,
May 1, 2009 @ 7:06 am
Think that I will just use Management Studio for the very rare moments that I may be scripting my database migrations. There is a more interesting migration happening over in the bird sanctuary at this time, and deleting their columns would be virtually impossible, Those whom make up these things are very aware of what they are willing to allow others to do with their brilliant work and must have mysterious reasons that are valid to them for doing whatever it is that they do to mess up others. Tip–flow with the go.
grampa and Maggykat Have a good day.