Altering a Default- unknown name...

SyetwemHi There.
I'd like to alter a DEFAULT I have set up on a database table.
Unfortunately, I don't know the name of the default as it was created when
the table was created without specifying a name.
Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
System tables)?...
Or is there a more straightforward way to alter a DEFAULT?
any help would be appreciated!
My final lines of code should look something like:
ALTER TABLE [dbo].[MyTable] DROP
DEFAULT <defaultname>FOR [Column1]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
GOselect OBJECT_NAME(constid),scl.name
FROM sysconstraints scr
INNER JOIN syscolumns scl
ON scr.id = scl.id
AND scr.colid = scl.colid
WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
AND scr.status = 2069
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
> SyetwemHi There.
> I'd like to alter a DEFAULT I have set up on a database table.
> Unfortunately, I don't know the name of the default as it was created when
> the table was created without specifying a name.
> Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
> System tables)?...
> Or is there a more straightforward way to alter a DEFAULT?
> any help would be appreciated!
> My final lines of code should look something like:
> ALTER TABLE [dbo].[MyTable] DROP
> DEFAULT <defaultname>FOR [Column1]
> GO
> ALTER TABLE [dbo].[MyTable] ADD
> CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
> GO
>|||Thanks - I just tried that though and it seems to only return defaults that
have been named explicitly (If I remove the WHERE clause I can see a list of
all explicitly created defaults from my database)
perhaps there's a way of dropping the default on a column without knowing
the name?...
"Roji. P. Thomas" wrote:

> select OBJECT_NAME(constid),scl.name
> FROM sysconstraints scr
> INNER JOIN syscolumns scl
> ON scr.id = scl.id
> AND scr.colid = scl.colid
> WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
> AND scr.status = 2069
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "len" <len@.discussions.microsoft.com> wrote in message
> news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
>
>|||> perhaps there's a way of dropping the default on a column without knowing
> the name?...
Here you go
DECLARE @.defname VARCHAR(100), @.cmd VARCHAR(1000)
SET @.defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'YourTableName'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.YourTableName) AND
name = 'YourColumnName'))
SET @.cmd = 'ALTER TABLE YourTableName DROP CONSTRAINT '
+ @.defname
EXEC(@.cmd)
Its taken from the following article by Ron Talmage. Have a look
http://msdn.microsoft.com/library/d.../>
ql00a11.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
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.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||Thanks guys - they worked a treat!!sql