我有以下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约束名称是不同的。我该如何编写一个脚本来获取该名称，然后将其添加到我的脚本中？
虽然典型的最佳实践是始终明确地命名约束条件，但您可以从目录视图动态获取它们：
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 ...
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/
这篇文章介绍了如何使用SQL Server Management Studio查找主键。首先，打开对象资源管理器并展开数据库，然后展开表格。在表格下面有一个名为“键”的文件夹，展开它可以看到所有的键，包括主键。双击主键即可查看主键详细信息。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);'
我的使用案例是更新由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应该包含旧的和新的约束名称。-- 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