Default Constraints (call: User-Defined Function or proc)

Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

DEFAULT constraint not working

I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

TRUNCATE TABLE NDRS_Call_Data_Table

INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View

The view is a complex join of several other tables. When I run the proc, I get the error:

"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

BUT, if I simply do this from Query Analyzer.....

INSERT INTO NDRS_Calls_Data_Table(data_indicator)
VALUES('Z')

it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.|||Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks|||Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.|||Originally posted by cvandemaele
Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.

Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.|||Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT ... SELECT syntax|||To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

example:

select isnull (title, 'No title') as title, au_lname
from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id|||True.

Undortunately, when using INSERT ... SELECT you can't say
SELECT ..., ISNULL(column, DEFAULT), ...

the way you can use the DEFAULT keyword like this...

INSERT
VALUES (x,y,z,DEFAULT,p,q,r)

You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.|||you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

declare @.value int
select @.value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o
where c.id=object_id('dbo.your_table')
and c.name=('your_column_name')
and o.type='d'
and m.id=c.cdefault
and m.id=o.id

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
Vitaliy
I'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan
|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last
>
|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>
|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first p
lace, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl..
.
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
..
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
..
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last[vbcol=seagreen]
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the cu
rrent name of the
constraint. You can then use dynamic SQL to drop the constraint. And then ad
d it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations whe
n
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>sql