I need to change current default values from df_curr_user to df_login_user
for about 600 differrent fileds in different tables. Should I do it by using
sp_unbindefault and sp_bindefault for each column? How can I do it
programmatically?
Thanks in advance for any help!
PerayuAre you talking about changing the default constraint name?
AMB
"Perayu" wrote:
> I need to change current default values from df_curr_user to df_login_user
> for about 600 differrent fileds in different tables. Should I do it by usi
ng
> sp_unbindefault and sp_bindefault for each column? How can I do it
> programmatically?
> Thanks in advance for any help!
>
> Perayu
>
>|||Not only the default constraint name. I want to replace it to a new one.
Actually, I can't change the name or update current used df_curr_user
because it is bound to columns. So, I defined a new one as df_login_user and
try to replace it. But have no idea how to do it programmatically.
Thanks.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
> Are you talking about changing the default constraint name?
>
> AMB
> "Perayu" wrote:
>|||See if this helps.
use northwind
go
create default df_current_user as current_user
go
create default df_login_user as suser_sname()
go
create table t1 (
c1 nvarchar(256)
)
go
create table t2 (
c1 nvarchar(256)
)
go
create table t3 (
c1 nvarchar(256)
)
go
create table t4 (
c1 nvarchar(256)
)
go
exec sp_bindefault 'df_current_user', 't1.c1'
exec sp_bindefault 'df_current_user', 't2.c1'
exec sp_bindefault 'df_current_user', 't3.c1'
exec sp_bindefault 'df_current_user', 't4.c1'
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as %'
go
declare @.sql nvarchar(4000)
declare c cursor local fast_forward
for
select
'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as%'
open c
while 1 = 1
begin
fetch next from c into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
exec sp_executesql @.sql
end
close c
deallocate c
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_login_user as %'
go
drop table t1, t2, t3, t4
go
drop default df_current_user, df_login_user
go
AMB
"Perayu" wrote:
> Not only the default constraint name. I want to replace it to a new one.
> Actually, I can't change the name or update current used df_curr_user
> because it is bound to columns. So, I defined a new one as df_login_user a
nd
> try to replace it. But have no idea how to do it programmatically.
> Thanks.
> Perayu
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
>
>|||It works like a charm!
Thank you so much.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E94A4876-0EF7-470B-8577-BF702B21B53F@.microsoft.com...
> See if this helps.
> use northwind
> go
> create default df_current_user as current_user
> go
> create default df_login_user as suser_sname()
> go
> create table t1 (
> c1 nvarchar(256)
> )
> go
> create table t2 (
> c1 nvarchar(256)
> )
> go
> create table t3 (
> c1 nvarchar(256)
> )
> go
> create table t4 (
> c1 nvarchar(256)
> )
> go
> exec sp_bindefault 'df_current_user', 't1.c1'
> exec sp_bindefault 'df_current_user', 't2.c1'
> exec sp_bindefault 'df_current_user', 't3.c1'
> exec sp_bindefault 'df_current_user', 't4.c1'
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as %'
> go
> declare @.sql nvarchar(4000)
> declare c cursor local fast_forward
> for
> select
> 'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as%'
> open c
> while 1 = 1
> begin
> fetch next from c into @.sql
> if @.@.error != 0 or @.@.fetch_status != 0 break
> exec sp_executesql @.sql
> set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
> exec sp_executesql @.sql
> end
> close c
> deallocate c
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_login_user as %'
> go
> drop table t1, t2, t3, t4
> go
> drop default df_current_user, df_login_user
> go
>
> AMB
> "Perayu" wrote:
>|||btw, does anyone know a 'clean' way to get the default value of a
default-bound column ?
for example:
create table t1(col varchar(10))
create default s as 'none'
sp_bindefault s, 't1.col'
I can find this value from information_schema.columns, like this:
select column_default
from information_schema.columns
where table_name = 't1'
and it returns:
create default s as 'none'
however, I believe extracting the string after the 'as' to get default
value isn't the best way. Can't imagine that sql server performs
inserts and calculates a default value on-the-flight
thanks,
Tam|||Sometime, the default value is not always a constant and must be done
on-the-flight. Like what I have is using SUSER_SNAME(), which will depends
on the login name.
Perayu
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125429897.170823.60630@.g43g2000cwa.googlegroups.com...
> btw, does anyone know a 'clean' way to get the default value of a
> default-bound column ?
> for example:
> create table t1(col varchar(10))
> create default s as 'none'
> sp_bindefault s, 't1.col'
> I can find this value from information_schema.columns, like this:
> select column_default
> from information_schema.columns
> where table_name = 't1'
> and it returns:
> create default s as 'none'
> however, I believe extracting the string after the 'as' to get default
> value isn't the best way. Can't imagine that sql server performs
> inserts and calculates a default value on-the-flight
> thanks,
> Tam
>|||Perayu - I agree, but there're times that default values are constant,
would it do on the flight anyway ?
sql
