change default value

How do you change a default value for a column in tsql?
cheers
Chalie.drop existing default constraint using statement.
ALTER TABLE <table>
DROP CONSTRAINT <default constraint name>
Recreate the new default constraint using :
ALTER TABLE <table> ADD CONSTRAINT
<constraint name> DEFAULT 'x' FOR <column name>
-Vishal
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> How do you change a default value for a column in tsql?
> cheers
> Chalie.
>|||What about something like this
CREATE TABLE DefVals(col1 int CONSTRAINT def_col1 DEFAULT 1, col2 int)
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
GO
ALTER TABLE DefVals DROP CONSTRAINT def_col1
GO
ALTER TABLE DefVals ADD CONSTRAINT def_col1 DEFAULT 4 FOR col1
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Hello Charlie !
The Default Value is a constraint that you can change:
Sop you have to DROP and ADD the new constraint.
HTH, Jens Süßmeyer.|||Hi,
Thanks for this, but how do I find out what my constraint name is?
Charlie
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> drop existing default constraint using statement.
> ALTER TABLE <table>
> DROP CONSTRAINT <default constraint name>
> Recreate the new default constraint using :
> ALTER TABLE <table> ADD CONSTRAINT
> <constraint name> DEFAULT 'x' FOR <column name>
>
> --
> -Vishal
> "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > How do you change a default value for a column in tsql?
> >
> > cheers
> > Chalie.
> >
> >
>|||Try
Exec sp_helpconstraint 'TABLE NAME'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Charlie,
EXEC sp_helpconstraint <tablename>
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:qRI3b.453$b82.171440@.newsfep1-win.server.ntli.net...
> Hi,
> Thanks for this, but how do I find out what my constraint name is?
> Charlie
> "Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
> news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> > drop existing default constraint using statement.
> >
> > ALTER TABLE <table>
> > DROP CONSTRAINT <default constraint name>
> >
> > Recreate the new default constraint using :
> > ALTER TABLE <table> ADD CONSTRAINT
> > <constraint name> DEFAULT 'x' FOR <column name>
> >
> >
> > --
> > -Vishal
> >
> > "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> > news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > > How do you change a default value for a column in tsql?
> > >
> > > cheers
> > > Chalie.
> > >
> > >
> >
> >
>

challenge...

How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?

Like I have a table employee

Employee

(

colA int

colB int

colC varchar

)

If i run the tsql..

it should give me

Employee

(

colA varchar

colB varchar

colC varchar

)

Is the table empty? If not, what is the disposition of the data that is already in the table?

|||

Well you could try using a change script generated by Enterprise Manager, like this one:

Code Snippet

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT

|||

Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.