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,
Pieter
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
>
|||hi,
Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY,
and not for DEFAULT :-(
any other idea?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> 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...
> 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'
already
>
|||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/d...st raints.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 Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> already
>
|||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 Smeyer" <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/d...st raints.sql[vbcol=seagreen]
> 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...
to[vbcol=seagreen]
my
>