July 29, 2008 at 9:38 am
I've been tasked with adding default values on a number of different columns throughout our new-ish SQL db. In the past, it's been on thirty or forty columns and that's not a big problem to do by hand. Well, now I need to add a default value of 0 on all the money datatype fields, and there's hundreds of them. Way too many to work by hand.
The trouble is, I'm not strong enough with cursors (which I am assuming is what would be needed here) to know how to script this. I'm fairly sure I'll be needing to do this again for some other grouping of columns in the near future, and I do not relish the though of doing all this by hand. Does anyone have some sample scripts for this they can throw my way, or at least a hearty shove in the right direction?
July 29, 2008 at 2:00 pm
this will generate an alter table statements -- i just whipped this up so its not tested, but should get you started. If I have some time tomorrow I'll check into it -- looks like a useful script I might need to add to my library.
======EDIT======
this will be a lot better for you - it tells if you there is an existing default value on the field so you can investigate those before running the alter statement. Or you can modify further to make a statement to remove the default constraint with the cs.name field.
select
case when cs.name is null then 0
when cs.name is not null then 1end existingDFExists,
cs.name existingDF,
'ALTER TABLE ' + s.name + '.' + ta.name +
' ADD CONSTRAINT DF_' + ta.name + '_' + c.name + ' DEFAULT 0 FOR ' + c.name
--select s.name, ta.name, c.name, t.name, *
from sys.schemas s inner join
sys.tables ta on s.schema_id = ta.schema_id inner join
sys.columns c on ta.object_id = c.object_id inner join
sys.types t on t.system_type_id = c.system_type_id left join
sys.default_constraints cs on c.column_id = cs.parent_column_id
and c.object_id = cs.parent_object_id
where t.name = 'money'
order by case when cs.name is null then 0
when cs.name is not null then 1 end
July 29, 2008 at 2:19 pm
Oh, nice. That looks a lot easier than what I was trying to futz with. I have a script that will rename any table fitting certain criteria, and I had been trying to modify that to fit this need and not having a lot of luck.
As it happens, I just had to go through and do about 80 of these by hand, so I'm definitely hoping this works. Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply