This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

use this SQL

jimi.cui(jimi)

select a.*
FROM syscolumns a
inner join sysobjects b
on a.id=b.id and b.xtype='U'
and b.name<>'dtproperties'
where exists(SELECT 1
FROM sysobjects
where xtype='PK'
and name in (
SELECT name
FROM sysindexes
WHERE indid in(
SELECT indid
FROM sysindexkeys
WHERE id = a.id
AND colid=a.colid
)
)
)
and b.name='_TableName'

(#3804340@0)
Last Updated: 2007-7-13
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 专业技术讨论 / 请教SQL Server的Primary Key问题 -sonican(田园); 2007-7-10 {215} (#3796113@0)
Primary key was created by below statement:
ALTER TABLE myTable ADD PRIMARY KEY (col ASC)
Q: How to drop this primary key using T-SQL? I have tried "ALTER TABLE myTable DROP PRIMARY KEY" but it did not work.

DROP CONSTRAINT PKName -carolhu(晒太阳的大花猫); 2007-7-11 (#3797211@0)

谢谢。我的问题是：PKName是随机生成的，但我不想在T-SQL中hardcode这个PKName，咋办？ -sonican(田园); 2007-7-11 (#3798053@0)

Try following code: -deep_blue(BLUE); 2007-7-11 {203} (#3798327@0)
DECLARE @PK_NAME VARCHAR(50)
SELECT @PK_NAME = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME ='YourTableName' AND CONSTRAINT_NAME LIKE 'PK_%'

Then you can drop it.

曾试过ALTER TABLE pub_User_Groups DROP CONSTRAINT @constraintName，但不行： Incorrect syntax near '@constraintName' -sonican(田园); 2007-7-11 (#3798972@0)

Using dynamic SQL -deep_blue(BLUE); 2007-7-12 {127} (#3800086@0)
Declare @sql VARCHAR(200)
SELECT @sql = ‘ALTER TABLE pub_User_Groups DROP CONSTRAINT ‘ + @constraintName
EXEC (@sql)

HTH

It works. 谢谢深蓝, 大花猫和jimi. -sonican(田园); 2007-7-12 (#3800251@0)

PK 名在 exec sp_MStablekeys TableName,null,14 中可以取到,希望对你有帮助 -jimi.cui(jimi); 2007-7-11 (#3799392@0)
use this SQL -jimi.cui(jimi); 2007-7-13 {618} (#3804340@0)
select a.*
FROM syscolumns a
inner join sysobjects b
on a.id=b.id and b.xtype='U'
and b.name<>'dtproperties'
where exists(SELECT 1
FROM sysobjects
where xtype='PK'
and name in (
SELECT name
FROM sysindexes
WHERE indid in(
SELECT indid
FROM sysindexkeys
WHERE id = a.id
AND colid=a.colid
)
)
)
and b.name='_TableName'

why-- alter table nums drop constraint @PK_NAME doesn't work? -hieveryone(:-)); 2007-7-16 (#3811023@0)
Why does the following not work? -hieveryone(:-)); 2007-7-16 {343} (#3811085@0)
alter table nums add primary key (n)

DECLARE @PK_NAME VARCHAR(50)
SELECT @PK_NAME = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME ='nums' AND CONSTRAINT_NAME LIKE 'PK_%'

alter table nums drop constraint @PK_NAME

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@PK_NAME'.

In command, alter table nums drop constraint PK_Name, PK_Name actually is an object rather than a varchar variable. Therefore, when PK_Name is a variable, you need use dynamic SQL: Exec (‘alter table nums drop constraint ‘ + @PK_NAME). HTH -deep_blue(BLUE); 2007-7-17 (#3811506@0)

@Florida

use this SQL

Replies, comments and Discussions:

More Topics