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

Allowing for blank fields in date text boxes

Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).
My question is as follows: How is it possible to leave a date textbox field blank, and either send 00\00\0000 or spaces to the SQL Server 2000 database datetime field?
Any advice/insight is appreciated! RobEDIT
Both Datetime and SmallDatetime allow NULLs Datetime is 8bytes while Smalldatetime is 4bytes but its resolution is limited. If you want Time Span you have to use DateTime.
when you are using NULL with Datetime you must use COUNT (*) in all you calculations because all SQL Server aggregate functions ignore NULLs except COUNT (*).
Hope this helps.|||Check outthis article