Dropping constraint on temporary table

I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @.name
set @.name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @.name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:DROP TABLE #teFotograferen-PatP|||Pat

That's exactly what defines my problem
If i run
DROP TABLE #teFotograferen

i get
Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

because the table was a temporary table and there's no way to get back in the scope where it was defined.

If i recreate the table and then drop it the constraint still remains in my database.

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
because it is not the same table

on the other hand

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
There is already an object named 'UniqueFields' in the database.

In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

It is not a design problem in my application, i just put some garbage in that i can't get out|||This kind of problem is symptomatic of multiple sub-problems.That comment wasn't an accident.

One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

The solution to this problem is to:

a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
b) Create the constraint with a default name (which is unique for each execution).

This should get you far enough to find the next problem!

-PatP|||[smacks forehead]

why would you need contraints on a temp table?

[/smacks forehead]|||After a restart of the server the offending constraint was gone.

Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

Thanks for the advice and input. :beer:

Dropping all foreign keys

I would like to have a procedure that I can call that will drop all foreign
keys. I can get the constraint name from sysobjects if I select all fkey
constraints, but how can I get the associated table name so that I can plug
that into an alter table statement?
I appreciate any suggestions.
ThanksSee the first query at http://www.aspfaq.com/2520
Just change it from "SELECT FK_Table ... FROM" to the following:
SELECT 'ALTER TABLE '+FK.TABLE_NAME+' DROP CONSTRAINT '+C.CONSTRAINT_NAME
FROM
Run it in query analyzer and you will generate a script in the bottom pane,
which you can then copy and run in a new Query Analyzer window.
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks
Here is one that I wrote. It's not pretty and it's not optimized, but it
works.
Rick Sawtell
----
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END|||WHY? Do you really want to sail through the windshield?
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
> I would like to have a procedure that I can call that will drop all
foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||*All* of them? I just hope some of them will make it back... someday.
ML

dropping a constraint

I think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...

I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?

Thanks for your help

Graham

Graham,

You can use sp_addscriptexec to post a SQL script to all subscribers of your publication with all the constraint changes you want to make.

http://msdn2.microsoft.com/en-us/library/ms147904.aspx

http://msdn2.microsoft.com/en-us/library/ms174360.aspx

Regards,

Gary

droping a CONSTRAINT

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helpingYou do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

It will show you exactly what to do|||I get an incredible script !!

I really dont understant
here I dop the column

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

then it doesnt exist any more ! why I cannot create it after ?|||because u failed to drop it....... u got error message :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Drop Primary key constraint of (#) Hash

Hi there,

I have created a hash table. After using it, somehow the primary key
constraint of this hash table still exist in database. Which cause
error.

When I delete this constraint with Alter table Drop con...
It gives no table exist error.

Can anybody give any idea.

Thanks in Adv.,
T.S.Negi> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.

I assume that by "hash table" you mean a temporary table? What makes you
think that the PK exists after the table is dropped? (Don't trust the output
of Enterprise Manager as that isn't always refreshed when you would expect
it to be) What error is caused and what is the code that produces the error?

--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0408102110.40ec8809@.posting.google.com>...
> Hi there,
> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.
> When I delete this constraint with Alter table Drop con...
> It gives no table exist error.
>
> Can anybody give any idea.
> Thanks in Adv.,
> T.S.Negi

I don't quite understand your issue - this works fine for me:

create table #t (col1 int not null)
alter table #t add constraint PK_t primary key (col1)
alter table #t drop constraint PK_t
drop table #t

It would be best to post your version of MSSQL, the exact error
messages you get, as well as some explanation of why you believe the
constraint exists but the table doesn't.

Simon

drop not null

hi

i'm using mssql server 2000

i want to remove a not null column constraint from the column
answertext in table answertext.

i tried the following line
ALTER TABLE AnswerText ALTER COLUMN AnswerText DROP NOT NULL;

it didn't work and this error message apeared (sorry about the german)
Server: Nachr.-Nr. 156, Schweregrad 15, Status 1, Zeile 6
Falsche Syntax in der Nhe des NOT-Schlsselwortes.

is there a way to drop not null column constraint or do i have to save
the data, drop the table and recreate it?

i created the table with the following ddl code
CREATE TABLE AnswerText(
...
,AnswerText VARCHAR(512) NOT NULL
...
)

tanks for your helpcreate table foo (blah varchar(10) not null)

alter table foo alter column blah varchar(10)null

I found the best way to learn stuff like this is either find it in BOL
or run prfiler on myself and do it in enterprise manager and then look
at the syntax.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

drop constraint?

Hi all,
How does one drop a constraint using an sql statement?
Thanke,
IvanALTER TABLE <TABLENAME>
DROP CONSTRAINT <ConstrintName>
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:u2SHNxHrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> How does one drop a constraint using an sql statement?
> Thanke,
> Ivan
>|||Ivan
use tempdb
CREATE TABLE #t
(
col DATETIME DEFAULT GETDATE()
)
GO
--sp_helpconstraint '#t'
ALTER TABLE #t DROP constraint DF__#t__col__6FDB4B23
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:u2SHNxHrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> How does one drop a constraint using an sql statement?
> Thanke,
> Ivan
>|||Thanks!
Ivan
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u1t834HrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Ivan
> use tempdb
> CREATE TABLE #t
> (
> col DATETIME DEFAULT GETDATE()
> )
> GO
> --sp_helpconstraint '#t'
> ALTER TABLE #t DROP constraint DF__#t__col__6FDB4B23
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:u2SHNxHrFHA.3060@.TK2MSFTNGP09.phx.gbl...
>|||Best to name the constraint (though you probably knew that, just wanted to
make sure Ivan did)
use tempdb
CREATE TABLE #t
(
col DATETIME CONSTRAINT dftl_t_col DEFAULT GETDATE()
)
GO
ALTER TABLE #t DROP constraint dftl_t_col
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1t834HrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Ivan
> use tempdb
> CREATE TABLE #t
> (
> col DATETIME DEFAULT GETDATE()
> )
> GO
> --sp_helpconstraint '#t'
> ALTER TABLE #t DROP constraint DF__#t__col__6FDB4B23
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:u2SHNxHrFHA.3060@.TK2MSFTNGP09.phx.gbl...
>

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?
Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

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

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 Column with Constraint

Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais

ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?

Seems your code is OK:

IF OBJECT_ID('T1') IS NOT NULL
drop table T1;

IF OBJECT_ID('T2') IS NOT NULL
drop table T2;

Create Table T1 (
num int primary key,
[Name] sysname
)

Create Table T2 (
Id int primary key,
num int,
Constraint T2_FK Foreign Key(num) References T1(num)
)

BEGIN TRANSACTION
ALTER TABLE T2 DROP CONSTRAINT T2_FK
ALTER TABLE T2 DROP COLUMN num;
COMMIT

The reason it require drop constraint first, is because T2_FK depends on column num,
Just like you can't drop a table if the table reference it not droped.

Thanks,

zuomin

|||

It should be work because the ALTER TABLE has an implicit transactions. More , I tested your situations and the things worked good. I thing your error come to the other part.

|||Yes my mistake. The error was coming from default constraint.. nevermind it! Tks for your help!

drop and create constraint

Hi There,
I've to alter the default constraint, So i drop then create agains. Before
applying this changes to all database, do i need to tell users to log off. o
r
will it work even they are connected to the system
Thanks
GaneshOn Thu, 18 May 2006 09:11:01 -0700, Ganesh wrote:

>Hi There,
>I've to alter the default constraint, So i drop then create agains. Before
>applying this changes to all database, do i need to tell users to log off.
or
>will it work even they are connected to the system
Hi Ganesh,
You can drop and create constraints while users are connected to the
database.
That being said - if possible, do this during maintenance window or
during times of low usage. The creation of the constraint (except a
DEFAULT constraint) will also cause SQL Server to check all existing
data . On a big table, this will take some time - and others won't be
able to access the table during that time.
Hugo Kornelis, SQL Server MVP

Drop a column

You just need to drop the default first:
ALTER TABLE dbo.ObjetName
DROP CONSTRAINT DF__ObjetName__ColumnName__41DEAF21
GO
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you very much Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d5801c4fd6d$739195c0$a301280a@.phx.gbl...
> You just need to drop the default first:
> ALTER TABLE dbo.ObjetName
> DROP CONSTRAINT DF__ObjetName__ColumnName__41DEAF21
> GO
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Now I can drop the constraint and the column in the publicator.
I run the snapshot, but when I try start sincronizing, it's give me this
error
The schema script 'exec sp_repldropcolumn '[dbo].[TableName]', 'ColumnName',
1' could not be propagated to the subscriber. The step failed.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d5801c4fd6d$739195c0$a301280a@.phx.gbl...
> You just need to drop the default first:
> ALTER TABLE dbo.ObjetName
> DROP CONSTRAINT DF__ObjetName__ColumnName__41DEAF21
> GO
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
you only need to run the snapshot agent if you're resynchronizing - and not
here. I suspect you have indexes/defaults on the subscriber that are
preventing the alter table working there. Now that the alter table is in the
queue, you'll have to remopve these constraints by hand before
synchronizing, or reinitialize. If you choose the former, it should be
obvious what is blocking the subscriber, but if not, have a look at logging:
http://support.microsoft.com/?id=312292
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)