how to know if a DEFAULT exists or not

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,
PieterIF 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" <pietercoucke@.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
>|||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" <pietercoucke@.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
> >
> >
>|||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/
"DraguVaso" <pietercoucke@.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
>|||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
--
"DraguVaso" <pietercoucke@.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" <pietercoucke@.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
>> >
>> >
>>
>|||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
"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
>
>|||Ok thanks! Works fine!
The solution of Alejandro works fine too! thanks a lot guys!
"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
> to change to standard beaviour of the Information Views,
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DraguVaso" <pietercoucke@.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" <pietercoucke@.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
> >> >
> >> >
> >>
> >>
> >
> >
>