how to know if a DEFAULT exists or not

9 May 2005 3:01 PM

DraguVaso

For a script that should update some table-definitions, I would like to know
how i can now if a DEFAULT exists for a given row in a table.

for exemple:
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'

This adds the Default NEWID() to the row MyRow in the table MyTable.
But when i run this query a second time, i get a warning ("Column already
has a DEFAULT bound to it.") that it already exists. So I would like my
query check first if it is there or not, and if not add the Default.

How do i do this? I can't find anything on google :-/

Thanks,

Pieter

9 May 2005 3:15 PM

Jens Süßmeyer

IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
news:Oct$xeKVFHA.2520@TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to
> know
> how i can now if a DEFAULT exists for a given row in a table.
>
> for exemple:

IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
Show quoteHide quote

>
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
>
> How do i do this? I can't find anything on google :-/
>
> Thanks,
>
> Pieter
>
>

9 May 2005 3:44 PM

DraguVaso

hi,

Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY,
and not for DEFAULT :-(

any other idea?

Show quoteHide quote

"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ew4NznKVFHA.3540@TK2MSFTNGP15.phx.gbl...
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
>       ALTER TABLE MyTable ADD
>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
> "DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
> news:Oct$xeKVFHA.2520@TK2MSFTNGP09.phx.gbl...
> > For a script that should update some table-definitions, I would like to
> > know
> > how i can now if a DEFAULT exists for a given row in a table.
> >
> > for exemple:
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
>       ALTER TABLE MyTable ADD
>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> >
> > This adds the Default NEWID() to the row MyRow in the table MyTable.
> > But when i run this query a second time, i get a warning ("Column
already
> > has a DEFAULT bound to it.") that it already exists. So I would like my
> > query check first if it is there or not, and if not add the Default.
> >
> > How do i do this? I can't find anything on google :-/
> >
> > Thanks,
> >
> > Pieter
> >
> >
>
>

9 May 2005 4:08 PM

Jens Süßmeyer

Your are right, you can try that from Dejan Serka:

select
db_name()    as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name    as CONSTRAINT_NAME
,com.text    as DEFAULT_CLAUSE
from
sysobjects c_obj
,syscomments com
where
c_obj.uid = user_id()
and c_obj.id = com.id
and c_obj.xtype = 'D'

Found under

http://sql.reproms.si/data/podatki/dejan/INFORMATION_SCHEMA%20Views%20for%20Default%20Constraints.sql

to change to standard beaviour of the Information Views,

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quoteHide quote

"DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
news:OluNH3KVFHA.1452@TK2MSFTNGP14.phx.gbl...
> hi,
>
> Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN
> KEY,
> and not for DEFAULT :-(
>
> any other idea?
>
> "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ew4NznKVFHA.3540@TK2MSFTNGP15.phx.gbl...
>> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
>> Constraint_Name like 'DF_MyTable_MyRow'
>>       ALTER TABLE MyTable ADD
>>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
>>
>> HTH, Jens Suessmeyer.
>>
>> ---
>> http://www.sqlserver2005.de
>> ---
>>
>>
>> "DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
>> news:Oct$xeKVFHA.2520@TK2MSFTNGP09.phx.gbl...
>> > For a script that should update some table-definitions, I would like to
>> > know
>> > how i can now if a DEFAULT exists for a given row in a table.
>> >
>> > for exemple:
>> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
>> Constraint_Name like 'DF_MyTable_MyRow'
>>       ALTER TABLE MyTable ADD
>>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
>> >
>> > This adds the Default NEWID() to the row MyRow in the table MyTable.
>> > But when i run this query a second time, i get a warning ("Column
> already
>> > has a DEFAULT bound to it.") that it already exists. So I would like my
>> > query check first if it is there or not, and if not add the Default.
>> >
>> > How do i do this? I can't find anything on google :-/
>> >
>> > Thanks,
>> >
>> > Pieter
>> >
>> >
>>
>>
>
>

10 May 2005 7:32 AM

DraguVaso

Ok thanks! Works fine!
The solution of Alejandro works fine too! thanks a lot guys!

Show quoteHide quote

"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23BWTRFLVFHA.3152@TK2MSFTNGP12.phx.gbl...
> Your are right, you can try that from Dejan Serka:
>
> select
> db_name()    as CONSTRAINT_CATALOG
> ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
> ,c_obj.name    as CONSTRAINT_NAME
> ,com.text    as DEFAULT_CLAUSE
> from
> sysobjects c_obj
> ,syscomments com
> where
> c_obj.uid = user_id()
> and c_obj.id = com.id
> and c_obj.xtype = 'D'
>
> Found under
>
>

http://sql.reproms.si/data/podatki/dejan/INFORMATION_SCHEMA%20Views%20for%20Default%20Constraints.sql
Show quoteHide quote

>
> to change to standard beaviour of the Information Views,
>
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
> "DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
> news:OluNH3KVFHA.1452@TK2MSFTNGP14.phx.gbl...
> > hi,
> >
> > Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN
> > KEY,
> > and not for DEFAULT :-(
> >
> > any other idea?
> >
> > "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote
> > in
> > message news:ew4NznKVFHA.3540@TK2MSFTNGP15.phx.gbl...
> >> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> >> Constraint_Name like 'DF_MyTable_MyRow'
> >>       ALTER TABLE MyTable ADD
> >>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> >>
> >> HTH, Jens Suessmeyer.
> >>
> >> ---
> >> http://www.sqlserver2005.de
> >> ---
> >>
> >>
> >> "DraguVaso" <pietercou***@hotmail.com> schrieb im Newsbeitrag
> >> news:Oct$xeKVFHA.2520@TK2MSFTNGP09.phx.gbl...
> >> > For a script that should update some table-definitions, I would like
to
> >> > know
> >> > how i can now if a DEFAULT exists for a given row in a table.
> >> >
> >> > for exemple:
> >> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> >> Constraint_Name like 'DF_MyTable_MyRow'
> >>       ALTER TABLE MyTable ADD
> >>     CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> >> >
> >> > This adds the Default NEWID() to the row MyRow in the table MyTable.
> >> > But when i run this query a second time, i get a warning ("Column
> > already
> >> > has a DEFAULT bound to it.") that it already exists. So I would like
my
> >> > query check first if it is there or not, and if not add the Default.
> >> >
> >> > How do i do this? I can't find anything on google :-/
> >> >
> >> > Thanks,
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>
>

9 May 2005 4:01 PM

Tibor Karaszi

create table t(c1 int default 1)
GO
select *
from sysobjects so inner join sysconstraints sc on so.id = sc.id
where so.id = object_id('t')

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"DraguVaso" <pietercou***@hotmail.com> wrote in message
news:Oct$xeKVFHA.2520@TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to know
> how i can now if a DEFAULT exists for a given row in a table.
>
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
>
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
>
> How do i do this? I can't find anything on google :-/
>
> Thanks,
>
> Pieter
>
>

9 May 2005 4:09 PM

Alejandro Mesa

Try,

declare @sql nvarchar(4000)
declare @cnstname sysname

select
    @cnstname = [name]
from
    sysobjects as so
where
    xtype = 'D'
    and parent_obj = object_id('dbo.t')
    and col_name(parent_obj, info) = 'colB'

if @cnstname is not null
    print @cnstname
else
    print 'no default for column [colB] in table [t].'
go

AMB

Show quoteHide quote

"DraguVaso" wrote:

> For a script that should update some table-definitions, I would like to know
> how i can now if a DEFAULT exists for a given row in a table.
>
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
>
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
>
> How do i do this? I can't find anything on google :-/
>
> Thanks,
>
> Pieter
>
>
>