在 SQL Server 2005 中动态查找主键约束

Question

我有以下 SQL：

 ALTER TABLE dbo.PS_userVariables DROP CONSTRAINT PK_PS_userVariables;
 ALTER TABLE dbo.PS_userVariables ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);

由于我有多个环境，因此 PK_PS_userVariables 约束名称在不同的数据库上是不同的。如何编写一个获取该名称然后将其添加到我的脚本中的脚本？

Answer 1

虽然典型的最佳实践是始终显式命名约束，但您可以从目录视图动态获取它们：

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.PS_userVariables';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

ALTER TABLE dbo.PS_userVariables ADD CONSTRAINT ...

Answer 2

SELECT 
   A.TABLE_NAME, 
   A.CONSTRAINT_NAME, 
   B.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
      CONSTRAINT_TYPE = 'PRIMARY KEY' 
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 
   A.TABLE_NAME 

参考：Pinal Dave @ http://blog.sqlauthority.com/2008/09/06/sql-server-find-primary-key-using-sql-server-management-studio/

Answer 3

DECLARE @TableName varchar(128)
DECLARE @IndexName varchar(128)
DECLARE @Command varchar(1000)

SET @TableName = 'PS_userVariables'

SELECT @IndexName = si.name
FROM sys.tables st
JOIN sys.indexes si ON st.object_id = si.object_id
WHERE st.name = @TableName
  AND si.is_primary_key = 1

SET @Command = 'ALTER TABLE dbo.' + QUOTENAME(@Tablename) + ' DROP CONSTRAINT ' + QUOTENAME(@IndexName) + ';
ALTER TABLE dbo.' + QUOTENAME(@Tablename) + ' ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);'

Answer 4

我的用例是更新 Entity Framework 6 生成的主键约束名称，以匹配 Entity Framework Core 的主键命名约定。

由于 EF 使用迁移，我创建了 Up 和 Down 迁移脚本，并创建了一个临时的长期表来存储新旧约束名称，以便我可以滚动如果需要的话回来。

这是我用来更新主键约束名称的 SQL：

-- create a temporary long-lived table
-- it can be deleted when rollback is no longer needed
CREATE TABLE dbo.__OldPrimaryKeyConstraintNames (
  SchemaName NVARCHAR(128) NOT NULL DEFAULT 'dbo',
  TableName NVARCHAR(128) NOT NULL,
  OldPrimaryKeyConstraintName NVARCHAR(128) NOT NULL,
  NewPrimaryKeyConstraintName NVARCHAR(128) NOT NULL
);

-- create a temporary table to hold the data for the script
DECLARE @tbl TABLE (SchemaName NVARCHAR(3), TableName NVARCHAR(128), PrimaryKeyConstraintName NVARCHAR(128));

-- get all primary key constraint names as well as it's schema and table
INSERT INTO @tbl
SELECT SCHEMA_NAME(pk.schema_id), t.name, pk.name
FROM sys.key_constraints pk
INNER JOIN sys.objects t on t.object_id = pk.parent_object_id
WHERE  pk.type = 'PK'

-- row count used for iterating through @tbl
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);

-- variables used when used for iterating through @tbl
DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @OldPrimaryKeyConstraintName NVARCHAR(128)
DECLARE @NewPrimaryKeyConstraintName NVARCHAR(128)
DECLARE @RenameSql NVARCHAR(MAX)

WHILE @RowCount > 0 BEGIN
  -- get the primary key constraint name, schema, and table name for this iteration
  SELECT @SchemaName = SchemaName, @TableName = TableName, @OldPrimaryKeyConstraintName = PrimaryKeyConstraintName, @NewPrimaryKeyConstraintName = CONCAT('PK_', TableName)
  FROM @tbl
  ORDER BY PrimaryKeyConstraintName DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

  -- store the old and new primary key constraint names
  INSERT __OldPrimaryKeyConstraintNames (SchemaName, TableName, OldPrimaryKeyConstraintName, NewPrimaryKeyConstraintName)
  VALUES (@SchemaName, @TableName, @OldPrimaryKeyConstraintName, @NewPrimaryKeyConstraintName)

  -- perform the rename
  SET @RenameSql = 'sp_rename ' + '''' + @SchemaName + '.' + QUOTENAME(@OldPrimaryKeyConstraintName) + '''' + ', ' + '''' + @NewPrimaryKeyConstraintName + ''''
  EXEC sp_executeSQL @RenameSql

  -- move to the next row
  SET @RowCount -= 1;
END

运行此脚本后，应使用旧的和新的约束名称填充 dbo.__OldPrimaryKeyConstraintNames。

这允许我们在出于任何原因需要时恢复重命名。

这是我用来恢复主键约束名称的 SQL：

-- create a temporary table to hold the data for the script
DECLARE @tbl TABLE (SchemaName NVARCHAR(3), OldPrimaryKeyConstraintName NVARCHAR(128), NewPrimaryKeyConstraintName NVARCHAR(128));

-- get the old and new constraint names as well as it's schema and table name 
INSERT INTO @tbl
SELECT SchemaName, OldPrimaryKeyConstraintName, NewPrimaryKeyConstraintName
FROM dbo.__OldPrimaryKeyConstraintNames

-- row count used for iterating through @tbl
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);

-- variables used when used for iterating through @tbl
DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @OldPrimaryKeyConstraintName NVARCHAR(128)
DECLARE @NewPrimaryKeyConstraintName NVARCHAR(128)
DECLARE @RenameSql NVARCHAR(MAX)

WHILE @RowCount > 0 BEGIN
  -- get the old and new constraint name and it's schema for this iteration
  SELECT @SchemaName = SchemaName, @OldPrimaryKeyConstraintName = OldPrimaryKeyConstraintName, @NewPrimaryKeyConstraintName = NewPrimaryKeyConstraintName
  FROM @tbl
  ORDER BY OldPrimaryKeyConstraintName DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

  -- revert the rename
  SET @RenameSql = 'sp_rename ' + '''' + @SchemaName + '.' + QUOTENAME(@NewPrimaryKeyConstraintName) + '''' + ', ' + '''' + @OldPrimaryKeyConstraintName + ''''
  SELECT @RenameSql
  EXEC sp_executeSQL @RenameSql

  -- move to the next row
  SET @RowCount -= 1;
END

-- drop the temporary long-lived table as it is not required 
DROP TABLE IF EXISTS dbo.__OldPrimaryKeyConstraintNames