Change default value

Hi,
How can i change default value in sql server through SQL script if i haven't
given default constraint name and using system default generated constraintg
name ?
I have 50 tables to change the default value of a particular field...pls
advice what to do...i don't want to open every table in design mode and
change the default value...pls suggest some script to do it.
With regards,
Gurmeet SinghThis scenario is exactly why you should name your constraint in the first
place...
If you want to automate this, you can use the system tables to get the
constraint name and use that name in your ALTER TABLE ... DROP CONSTRAINT
... statements. Note that SQL Server doesn't accept a variable for table or
constraint name, so you have do use dynamic SQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||Hi,
Refer books online for the below commands,
Alter table drop constraint <Const_name>
Alter table add constraint
Thanks
Hari
MCDBA
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||You can remove the default from a column using the following script:
-- remove the default
DECLARE @.constraint_name SYSNAME
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.name = '<table name>'
AND c_obj.xtype = 'D'
AND cols.[name] = '<column name>')
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
You can adjust this to use multiple tables and to add the new constraint as
well.
Jacco Schalkwijk
SQL Server MVP
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>