Drop column with default constraint

Hello
I have a table with column which have default constraint
(SQL server generated name of constraint).
Is there a way to drop this column without re-creating the table?
Example:
create table abc (xxx int, yyy int default 0);
alter table abc drop column yyy;
Thanks
TibXSee thread:
SQL Help with Drop Column
http://tinyurl.com/65nva
Bryce|||It's a good idea to give a default a meaningful name when you create
it. Query Analyzer will show you the name of the constraint in the
object browser (the name of a default begins with DF and includes the
table and column name). Then you can drop it in the usual way:
ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
ALTER TABLE abc DROP COLUMN yyy;
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
colA int,
colB varchar(25) default ('aaaaa')
)
go
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
begin
set @.sql = N'alter table dbo.t drop constraint ' + @.cnstname
execute sp_executesql @.sql
end
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
alter table dbo.t
add constraint df_t_colB default ('aaaaa') for colB with values
go
alter table dbo.t
drop constraint df_t_colB
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
create default df_t_colB as 'aaaaa'
go
execute sp_bindefault df_t_colB, 't.colB'
go
execute sp_unbindefault 't.colB'
go
drop default df_t_colB
go
drop table dbo.t
go
AMB
"TibX" wrote:

> Hello
> I have a table with column which have default constraint
> (SQL server generated name of constraint).
> Is there a way to drop this column without re-creating the table?
>
> Example:
> create table abc (xxx int, yyy int default 0);
> alter table abc drop column yyy;
>
> Thanks
> TibX
>|||The problem is that I have several installations
with generated name of that default constraint
and I need drop column by a script.
TibX
David Portas wrote:
> It's a good idea to give a default a meaningful name when you create
> it. Query Analyzer will show you the name of the constraint in the
> object browser (the name of a default begins with DF and includes the
> table and column name). Then you can drop it in the usual way:
> ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
> ALTER TABLE abc DROP COLUMN yyy;
>|||Try this:
DECLARE @.df SYSNAME
SET @.df =
(SELECT OBJECT_NAME(cdefault)
FROM SYSCOLUMNS
WHERE id = OBJECT_ID('dbo.abc')
AND name = 'yyy')
IF @.df IS NOT NULL
BEGIN
EXEC sp_rename @.df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.abc DROP CONSTRAINT df_to_drop
END
ALTER TABLE dbo.abc DROP COLUMN yyy
David Portas
SQL Server MVP
--|||Thanks for your solutions.
I use
select name from sysobjects ...
TibX

Drop & Create sProc

I generated SQL Script to drop and recreate some tables in my database
that I will want to perform monthly. I would like to put the script into a
sproc, however when I try to compile it I receive an error that the tables
and indexes already exist in my database.
Is there any way around this other than dropping all the tables before I
compile
the sproc?
Thanks,
MarcEncapsulate the CREATE / DROP statements within an IF ?
IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
DROP ....
CREATE ...
END
"Marc Miller" wrote:

> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script into
a
> sproc, however when I try to compile it I receive an error that the tables
> and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile
> the sproc?
> Thanks,
> Marc
>
>|||Marc Miller (mm1284@.hotmail.com) writes:
> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script
> into a sproc, however when I try to compile it I receive an error that
> the tables and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile the sproc?
Unless you are running SQL 6.5, you should not get that error.
I suspect that you have something that looks like:
CREATE PROCEDURE yoursp AS
CREATE TABLE abc
CREATE INDEX abc_ix ON abc(def)
go
CREATE TABLE xyz
CREATE INDEX xyz_ix ON xyz(www)
go
This is a script that first creates a procedure, and then creates a table.
This is because the script includes "go" which is an instruction to
the query tool that this is where the batch ends. "go" is not an SQL
command.
Thus, you would have to remove the "go" to include all code in the
stored procedure. However, you may then find that run into other
problems, because you have commands that must be in separate batches.
I would suggest that you store the script as a separate file. This is
would you should with stored procedures as well. The database should
be seen as a binary repository.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx