February 11, 2008 at 3:30 am
Hi.
When I create a constraint, it autogenerates its name. For example:
alter table X add primary key(id)
However, it seems possible to drop it only via its name, nothing like:
alter table X drop primary key
The only solution so far I came up with, is to explicitly name the constraint:
alter table X add constraint pk_X primary key (id)
However, there are a lot of existing constraints with autogenerated names and are named differently in production and test database, so you can't just re-execute the same script in production database.
Any solution short of manually recreate each contraint with a predefined name?
February 11, 2008 at 5:08 am
For constraints, indexes and foreign keys you can use sp_rename. Since these objects are rarely referenced by name in other textual database objects, and since they are mostly not textual database objects themselves (their definition is stored mostly as meta information, unlike with stored procedures, triggers, functions, ...), it is safe to use sp_rename.
Regards,
Andras
February 11, 2008 at 7:29 am
Thank you.
However, it doesn't help me much as is, because I need identify it without knowing the exact name.
But, I came up with a pretty solution - autogenerated names differ in last part, so I can use something like this:
declare @s-2 nvarchar(1024);
set @s-2=N'ALTER TABLE dbo.tableX DROP CONSTRAINT '+(select name from sysobjects where name like 'PK__Tablex%' and xtype='PK'
and parent_obj=(select id from sysobjects S2 where S2.name = 'TableX'));
Exec sp_executesql @s-2;
If there is no object, @s-2 is null and there's no error, if there's more than one, you get error "Subquery returned more than 1 value". A similar snippet can be used to rename constraints using sp_rename. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply