Constraint default

JUDAJIME,

Una columna puede tener default value por que se creo una restriccion
default o porque la columna fue ligada a un valor default mediante
sp_bindefault.

Ejemplo:

use northwind
go

create table t1 (
c1 int default(0)
)
go

select
table_name,
column_name,
column_default
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
and column_name = 'c1'
go

declare @sql nvarchar(4000)
declare @cn sysname

select
@cn = object_name(constid)
from
sysconstraints
where
[id] = object_id('dbo.t1')
and col_name([id], colid) = 'c1'
and objectproperty(constid, 'IsDefaultCnst') = 1

if not (@cn is null)
begin
set @sql = N'alter table dbo.t1 drop constraint [' + @cn + N']'
exec sp_executesql @sql
end
go

select
table_name,
column_name,
column_default
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
and column_name = 'c1'
go

create default df_uno as 1
go

exec sp_bindefault 'df_uno', 't1.c1'
go

select
table_name,
column_name,
column_default
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
and column_name = 'c1'
go

declare @sql nvarchar(4000)

set @sql = N''

select
@sql = 'exec sp_unbindefault ''' + table_name + '.' + column_name + ''''
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
and column_name = 'c1'
and column_default like '%create default %'

print @sql

if @sql is not null
exec sp_executesql @sql
go

select
table_name,
column_name,
column_default
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
and column_name = 'c1'
go

drop default df_uno
go

drop table t1
go


AMB



"JUDAJIME" wrote:

ordial saludo

Necesito, averiguar, si un campo tiene valor por defecto, si lo tienen
entonces eliminarlo. Como puedo hacer esto?

Saludos,
JUDAJIME