×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

use this SQL

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'
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 请教SQL Server的Primary Key问题
    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
      • 谢谢。我的问题是:PKName是随机生成的,但我不想在T-SQL中hardcode这个PKName,咋办?
        • Try following code:
          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'
            • Using dynamic SQL
              Declare @sql VARCHAR(200)
              SELECT @sql = ‘ALTER TABLE pub_User_Groups DROP CONSTRAINT ‘ + @constraintName
              EXEC (@sql)

              HTH
              • It works. 谢谢深蓝, 大花猫和jimi.
        • PK 名在 exec sp_MStablekeys TableName,null,14 中可以取到,希望对你有帮助
        • use this SQL
          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?
    • Why does the following not work?
      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