Changing default for existing column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Changing default for existing column

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-10 : 19:38:14

I just want to alter the default of a NVARCHAR field. From what I can understand of BO, I can't see how the default can be altered. So, instead, I tried to drop it and add it. Adding I can do but the dropping does not seem to work. What's wrong with my syntax? Thanks!

ALTER TABLE UserData DROP DEFAULT FOR Access
ALTER TABLE UserData ADD DEFAULT '000' FOR Access

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-10 : 19:45:47

You need to do:

ALTER TABLE UserData DROP CONSTRAINT ConstraintName

If you run sp_helpconstraint on that table you'll get the name of the constraint.

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-10 : 20:16:08

I ran sp_helpconstraint UserData and it returned only one record: Userdata. So I don't know what the name of the constraint might be.

I also checked the CONSTRAINT_COLUMN_USAGE view and the only constraints seem to be on the ID columns. But if I look in the Columns view or in the design of the table, it's clear that there is a default value assigned to the field.

And, of course, I can't add a default without taking the old one out.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 21:05:02

post the create staement for the table and I'm sure someone will tell you how to find the code for the drop.

If not post it again in about 16 hours when I get home and I'll have a look.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-10 : 22:30:08

The DB, the table and all its fields were created manually through an upgrade from an Access database several years ago, not by T-SQL.

Your suggestion got me thinking and I tried to drop the column entirely and recreate it from T-SQL. However, when I try this, I get: ALTER TABLE DROP COLUMN Access failed because DEFAULT CONSTRAINT DF_TEMPORARY_ACCES_1367E606 accesses this column. I tried dropping the default constraint using that ConstraintName (hey, anything's worth a try) and that didn't work either.

Kristen
Test

22859 Posts

Posted - 2004-12-11 : 00:12:38

Can you script the table for us to look at? (Enterprise manager : Generate SQL, or View Objects in Query Analyser and right-click and us Create Table)

Kristen

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-11 : 02:23:13

You can use Enterprise Manager to make this change. At the same time run Profiler to see what EM is doing behind the scenes in order to effect the change.

HTH

=================================================================

Happy Holidays!

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-11 : 12:16:37

Bustaz Kool: the change must be done via T-SQL. Doing it through EM is quite simple but not viable with hundreds of clients.

Kristen, here you go:

CREATE TABLE [UserData] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initials] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Timer] [smallint] NULL CONSTRAINT [DF__Temporary__Timer__182C9B23] DEFAULT (0),
[Existing] [smallint] NULL CONSTRAINT [DF__Temporary__Exist__1920BF5C] DEFAULT ((-1)),
[OpenedSession] [smallint] NULL CONSTRAINT [DF__Temporary__Opene__1A14E395] DEFAULT (0),
[LoggedOn] [smallint] NULL CONSTRAINT [DF__Temporary__Logge__1B0907CE] DEFAULT (0),
[AgendaAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Agend__1BFD2C07] DEFAULT (0),
[AnniversariesAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Anniv__1CF15040] DEFAULT (0),
[ChequesAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Chequ__1DE57479] DEFAULT (0),
[CallsAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Calls__1ED998B2] DEFAULT (0),
[OutstandingAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Outst__1FCDBCEB] DEFAULT (0),
[PatientListAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Patie__20C1E124] DEFAULT (0),
[PendingPlansAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Pendi__21B6055D] DEFAULT (0),
[Access] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Temporary__Acces__22AA2996] DEFAULT ('0000000000000000'),
[ActiveForm] [smallint] NULL CONSTRAINT [DF__Temporary__Activ__239E4DCF] DEFAULT (0),
[PendingClaimsAltered] [smallint] NULL CONSTRAINT [DF__Temporary__Pendi__24927208] DEFAULT (0),
CONSTRAINT [aaaaaUserData_PK] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-11 : 12:52:48

WindChaser,

I agree absolutely. My point was that you could use these two tools in tandem to find out the necessary sequence of steps. You use that knowledge to create a production level script.

I'm a strong advocate of scripting all objects within Query Analyzer. I have a series of templates that I use for each of the various entities. These scripts can then be put under source code control. Plus, now all my scripts have the proper headers, copyright notices, consistent look and feel, etc.

HTH

=================================================================

Happy Holidays!

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-11 : 13:33:26

Ha, gotcha.

So when I change the default manually and run SQL profiler, I see that EM does this:

Alter Table dbo.Userdata Drop Constraint DF__Temporary__Acces__22AA2996
Alter Table dbo.Userdata Add Constraint DF__Temporary__Acces__22AA2996 Default ('0000000') For Access

Great. But how can I find out what the name of the constraint is from client to client? On my machine, it's DF__Temporary__Acces__22AA2996, on theirs it's something else. Once I know it, I would give it a static name for future alterations.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-11 : 13:57:40

The INFORMATION_SCHEMA views will have all of the information you could need regarding constraints. Also, you can fix the problem by avoiding it entirely: modify your scripts to explicitly name all of your constraints. That also lets you enforce your own naming convention.

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-11 : 16:03:58

None of the views for this table contain this information i.e. that the constraint name is DF__Temporary__Acces__22AA2996. Would it be hidden? Is there a query that I can use to get this constraint name? Whereas future scripts go, I've learned my lesson.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-11 : 16:20:06

Crap. Don't know why that doesn't work, but the following will:

SELECT name FROM sysobjects WHERE type='D' AND parent_obj=object_id('myTable')

That will give you all defaults on the table.

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-12-11 : 17:23:39

Bingo. From here, the rest is a breeze.

Thanks Robvolk and everybody else!

Kristen
Test

22859 Posts

Posted - 2004-12-11 : 18:49:21

FWIW we always script all changes to the database (often by using Enterprise Manger "Design Table" and storing the script it wants to use).

Thus when we run the script we are sure to be using the same names for Objects on all database instances (and we rename any system-allcoated "Random" names to something more obvious)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources