我的使用案例是更新由Entity Framework 6生成的主键约束名称,以匹配Entity Framework Core的主键命名约定。
由于EF使用迁移,我创建了一个Up
和Down
迁移脚本,并创建了一个临时的长期存储表来存储旧的和新的约束名称,以便在需要时可以回滚。
这是我用来更新主键约束名称的SQL:
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
);
DECLARE @tbl TABLE (SchemaName NVARCHAR(3), TableName NVARCHAR(128), PrimaryKeyConstraintName NVARCHAR(128));
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'
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @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
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;
INSERT __OldPrimaryKeyConstraintNames (SchemaName, TableName, OldPrimaryKeyConstraintName, NewPrimaryKeyConstraintName)
VALUES (@SchemaName, @TableName, @OldPrimaryKeyConstraintName, @NewPrimaryKeyConstraintName)
SET @RenameSql = 'sp_rename ' + '''' + @SchemaName + '.' + QUOTENAME(@OldPrimaryKeyConstraintName) + '''' + ', ' + '''' + @NewPrimaryKeyConstraintName + ''''
EXEC sp_executeSQL @RenameSql
SET @RowCount -= 1;
END
运行此脚本后,
dbo.__OldPrimaryKeyConstraintNames
应该包含旧的和新的约束名称。
这样我们就可以在需要时恢复重命名操作。
以下是我用于恢复主键约束名称的SQL:
DECLARE @tbl TABLE (SchemaName NVARCHAR(3), OldPrimaryKeyConstraintName NVARCHAR(128), NewPrimaryKeyConstraintName NVARCHAR(128));
INSERT INTO @tbl
SELECT SchemaName, OldPrimaryKeyConstraintName, NewPrimaryKeyConstraintName
FROM dbo.__OldPrimaryKeyConstraintNames
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @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
SELECT @SchemaName = SchemaName, @OldPrimaryKeyConstraintName = OldPrimaryKeyConstraintName, @NewPrimaryKeyConstraintName = NewPrimaryKeyConstraintName
FROM @tbl
ORDER BY OldPrimaryKeyConstraintName DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
SET @RenameSql = 'sp_rename ' + '''' + @SchemaName + '.' + QUOTENAME(@NewPrimaryKeyConstraintName) + '''' + ', ' + '''' + @OldPrimaryKeyConstraintName + ''''
SELECT @RenameSql
EXEC sp_executeSQL @RenameSql
SET @RowCount -= 1;
END
DROP TABLE IF EXISTS dbo.__OldPrimaryKeyConstraintNames