Drop Column with default constraint in T-SQL

Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint nam
e
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!examnotes (Martijn@.discussions.microsoft.com) writes:
> I'm making an SQL script that has to drop some columns. The problem is
> that the column has a default value and therefore I can't use the DROP
> COLUMN directly. I've got to drop the 'default' constraint, but the
> problem is that we have to use this script on different database and
> then the constraint name is not always the same.
> (example:)
> DB1 -> DF_COLUMNX_ddf87d67s68
> DB2 -> DF_COLUMNX_ddf79djks90
> I know how to get the Constraint name but I can't use that as a variable
> in the DROP CONSTRAINT function.
> Has someone a solution for this problem? It has to be done with scripting!
SELECT @.default_name = o2.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = @.tbl
AND c.name = @.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You'll have to resort to dynamic SQL:
declare @.constraint varchar(8000), @.str varchar (8000)
set @.constraint = 'DF_COLUMNX_ddf87d67s68'
set @.str = 'alter table MyTable drop constraint ' + @.constraint
exec (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Martijn" <Martijn@.discussions.microsoft.com> wrote in message
news:3C62E773-C60A-46ED-A8A0-32E5AFDC7ACF@.microsoft.com...
Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint
name
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!|||To have more control over constraint names, create constraints using the
ALTER TABLE:
alter table owner.table
add constraint constraint_name
default (<default_value | default_expression> )
for column_name
with values
go
This way you control the names of constraints (in this case the name of the
default constraint).
ML