November 8, 2012 at 9:59 pm
I want to redo all of my default value constraints. Is there a quick way to do this? I have over 2500 constraints and it has taken 4 hours to drop 1000 of them. Any ideas?
Thank you.
Mike
November 9, 2012 at 12:34 am
Well, you could build a table with each db/schema/table/column in it, and then specify the default values to be used for each. Then you could use the table to generate dynamic SQL to create all your code for each constraint. It could be a lot of work coding and testing before you trusted it to do the job correctly. Sorry, but I have no better ideas.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 9, 2012 at 12:45 am
why are you doing this?
Are you looking to rename all the constraints or to change them?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 9, 2012 at 2:35 am
Hi Mike,
How you dropped only Default Constraints ?
Regards
Chaithanya M
November 9, 2012 at 2:58 am
I personally use a script to generate the drop constraints, this usually works and only needs tweaking
Select
'Alter Table ['+s.name+'].['+o.name+'] Drop Constraint ' +c.name
from sys.default_constraints c
JOIN sys.objects o on o.object_id=c.parent_object_id
JOin sys.schemas s on s.schema_id=o.schema_id
Join sys.columns col on col.column_id=c.parent_column_id
and col.object_id=c.parent_object_id
Where s.name != 'sys'
the Last time I did this I had to also rename them from the default name (DF_<table>_<HEX valued object_id>) to a proscribed format of DF_<schema>_<Table>_<Column>
So generated this bit of code,
Select
'Alter Table ['+s.name+'].['+o.name+'] Drop Constraint ' +c.name
+Char(10)+'Alter Table ['+s.name+'].['+o.name+'] Add Constraint DF_'+s.name+'_'+o.name+'_'+col.name+' Default '+ c.definition + ' for '+ col.name
from sys.default_constraints c
JOIN sys.objects o on o.object_id=c.parent_object_id
JOin sys.schemas s on s.schema_id=o.schema_id
Join sys.columns col on col.column_id=c.parent_column_id
and col.object_id=c.parent_object_id
--Where
-- s.name=<insert target schema name>
-- and o.name=<insert target table name>
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 9, 2012 at 9:16 am
Jason-299789 (11/9/2012)
why are you doing this?Are you looking to rename all the constraints or to change them?
Yes, I want them named consistently so that it is easier in the future if I have to make changes.
Mike
November 9, 2012 at 9:17 am
Jason-299789 (11/9/2012)
I personally use a script to generate the drop constraints, this usually works and only needs tweaking
Select
'Alter Table ['+s.name+'].['+o.name+'] Drop Constraint ' +c.name
from sys.default_constraints c
JOIN sys.objects o on o.object_id=c.parent_object_id
JOin sys.schemas s on s.schema_id=o.schema_id
Join sys.columns col on col.column_id=c.parent_column_id
and col.object_id=c.parent_object_id
Where s.name != 'sys'
the Last time I did this I had to also rename them from the default name (DF_<table>_<HEX valued object_id>) to a proscribed format of DF_<schema>_<Table>_<Column>
So generated this bit of code,
Select
'Alter Table ['+s.name+'].['+o.name+'] Drop Constraint ' +c.name
+Char(10)+'Alter Table ['+s.name+'].['+o.name+'] Add Constraint DF_'+s.name+'_'+o.name+'_'+col.name+' Default '+ c.definition + ' for '+ col.name
from sys.default_constraints c
JOIN sys.objects o on o.object_id=c.parent_object_id
JOin sys.schemas s on s.schema_id=o.schema_id
Join sys.columns col on col.column_id=c.parent_column_id
and col.object_id=c.parent_object_id
--Where
-- s.name=<insert target schema name>
-- and o.name=<insert target table name>
With the code above, how did you get the default value into each constraint? Did it take a long time?
Mike
November 9, 2012 at 9:27 am
instead of dropping and recreating, it would be much faster and easier to generate code to simply rename the existing constraints.
this is what i would do:
/*
EXEC sp_rename 'DF__SFHEAD__MSLOAN__51278362','DF__SFHEAD_MSLOAN'
EXEC sp_rename 'DF__SFHEAD__URUSEAMT__2D8A0CC1','DF__SFHEAD_URUSEAMT'
EXEC sp_rename 'DF__GMINDEX__MSTABLE__4F3F3AF0','DF__GMINDEX_MSTABLE'
EXEC sp_rename 'DF__GMINDEX__SFTABLE__4A65773A','DF__GMINDEX_SFTABLE'
*/
Select
'EXEC sp_rename ''' + c.name + ''',''DF__' + o.name + '_' + col.name + ''''
from sys.default_constraints c
JOIN sys.objects o on o.object_id=c.parent_object_id
JOin sys.schemas s on s.schema_id=o.schema_id
Join sys.columns col on col.column_id=c.parent_column_id
and col.object_id=c.parent_object_id
Where s.name != 'sys'
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply