April 15, 2013 at 4:42 pm
Is there an easy way to find all the bound defaults on table objects.
We have an app where many of the tables have defaults bound to columns.
The defaults were created and bound using the soon to be phased out "create default ..." and "sp_bindefault"
When scripting the table these defaults do not show up ... the only way I know of to find the columns in the table that have these defaults is to go column by column in the Management Studio and look at the properties of the individual columns. [that is one of the reasons I have never used this method to put a default on a column]
There must be an easier way.
Is there someone out there that can point me in the right direction?
Thanking you in advance with much appreciation.
April 18, 2013 at 4:31 am
Ellen-477471 (4/15/2013)
Is there an easy way to find all the bound defaults on table objects.We have an app where many of the tables have defaults bound to columns.
The defaults were created and bound using the soon to be phased out "create default ..." and "sp_bindefault"
When scripting the table these defaults do not show up ... the only way I know of to find the columns in the table that have these defaults is to go column by column in the Management Studio and look at the properties of the individual columns. [that is one of the reasons I have never used this method to put a default on a column]
There must be an easier way.
Is there someone out there that can point me in the right direction?
Thanking you in advance with much appreciation.
There is an easier way, you're right!
I found this helpful post:
It uses some system tables and functions to get the information and convert it to SQL statements that can be used to unbind and drop the defaults, and create default constraints. Obviously you should test it on a copy of the live database first!
As a general rule, whatever you can do through the interface in Management Studio, you can do more efficiently via a SQL script as the system tables that it uses to get its information are available to us as well. As long as you bear that in mind, you should find problems like this easier to solve in the future.
Duncan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply