What is the easiest way to alter fields' default

Question

What is the easiest way to alter fields' default

halifaxdal

SSCoach

Points: 19741
More actions
August 2, 2013 at 6:54 am

#278170

I have a database with tables to be updated so that all varchar fields will be given a default '' value and all bit fields will be given 1
I am doing this way but am having some issue:
1. Loop each table
2. Loop each column
3. set @sql = 'ALTER TABLE ' + @TableName + 'add constraint df_Constraint' + @ColumnName + ' DEFAULT ' + '''' + '''' + ' FOR ' + @ColumnName
The problem is it fails when there is already existing constraint for a column.
And even if it is working, I am still wondering if there are better ways to do this?
Thank you in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

halifaxdal (8/2/2013)
I have a database with tables to be updated so that all varchar fields will be given a default '' value and all bit fields will be given 1
I am doing this way but am having some issue:
1. Loop each table
2. Loop each column
3. set @sql = 'ALTER TABLE ' + @TableName + 'add constraint df_Constraint' + @ColumnName + ' DEFAULT ' + '''' + '''' + ' FOR ' + @ColumnName
The problem is it fails when there is already existing constraint for a column.
And even if it is working, I am still wondering if there are better ways to do this?
Thank you in advance.

really you are trying to alter a constraint, and not the column...i don't believe ANY constraint allows an alter, so it's a drop and recreate kind of situation.

you'll have to drop the existing default constraint, and add a new one with the new value; so it's a two command drop/create instead of a single alter.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Thanks.

Then how do I loop each field and find if there is a constraint on it? and get the constraint's name if I have to put the name there when drop it, or can I just simply drop the constraint on a given field?

Lowell SSC Guru Points: 323518 More actions · Answer 3

basic example:

CREATE TABLE [dbo].[SALESDATES] (

[ID] INT IDENTITY(1,1) NOT NULL,

[SOMEDATA] VARCHAR(30) NULL,

[SALEDATE] DATETIME NULL DEFAULT (getdate()))

ALTER TABLE SalesDates DROP CONSTRAINT DF__SalesDate__saled__4ED6BD4A

ALTER TABLE SalesDates ADD CONSTRAINT DF__SalesDate__saled__4ED6BD4A DEFAULT (DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) ) FOR SALEDATE

--don't know the name? gotta look it up?

select

object_name(object_id) ,

object_name(parent_object_id),

*

from sys.objects

where type_desc = 'DEFAULT_CONSTRAINT'

and object_name(parent_object_id) = 'SALESDATES'

order by create_date desc

--this was specific to my table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Look good but the code

ALTER TABLE SalesDates DROP CONSTRAINT DF__SalesDate__saled__4ED6BD4A

indicates the script knows the constraint's name in advance.

Since there are lots of fields for each table, I can't manually put the name in the code. 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 5

halifaxdal (8/2/2013)
Thanks.
Then how do I loop each field and find if there is a constraint on it? and get the constraint's name if I have to put the name there when drop it, or can I just simply drop the constraint on a given field?

ok, a cursor that looks like this would get you started.

you cna use either the column data type and the value of the definition ('') for example

select

object_name(object_id) as TableName,

colz.name,

TYPE_NAME(colz.system_type_id),

def.name,def.definition

from sys.columns colz

INNER JOIN sys.default_constraints def

ON colz.[default_object_id] = def.[object_id]

where default_object_id <> 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

halifaxdal SSCoach Points: 19741 More actions · Answer 6

halifaxdal

SSCoach

Points: 19741

August 2, 2013 at 7:29 am

#1638161

Thank you, that's what I want

Lowell SSC Guru Points: 323518 More actions · Answer 7

ok, you have a requirement that is a little scary for me (ALL bit flas no matter what/all varchars no matter what)

*note this is changing ALL CURRENT DEFAULT VARCHAR FIELDS TO blank!

that's probably not right, i have defaults for Y/N column values, for example!

but here's a tested script that generates the commands i think

select

OBJECT_NAME(colz.object_id),

colz.name,

TYPE_NAME(colz.system_type_id),

def.name,def.definition,

CASE

WHEN def.[object_id] <> 0

THEN 'ALTER TABLE '

+ quotename(OBJECT_schema_NAME(colz.object_id) )

+'.'

+ quotename(OBJECT_NAME(colz.object_id))

+ ' DROP CONSTRAINT ' + def.name + ';'

ELSE ''

END

+ 'ALTER TABLE '

+ quotename(OBJECT_schema_NAME(colz.object_id) )

+'.'

+ quotename(OBJECT_NAME(colz.object_id))

+ ' ADD CONSTRAINT DF_'

+ OBJECT_NAME(colz.object_id)

+ '_'

+ colz.name

+ ' DEFAULT '

+ CASE

WHEN TYPE_NAME(colz.system_type_id) = 'bit'

THEN '1'

ELSE ' '''' '

END

+ ' FOR ' + quotename(colz.name)

from sys.columns colz

LEFT OUTER JOIN sys.default_constraints def

ON colz.[default_object_id] = def.[object_id]

where TYPE_NAME(colz.system_type_id) IN('varchar','bit')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!