我在数据库中重命名了一个表,使用的语句是
EXEC sp_rename 'tblOldAndBusted', 'tblNewAndShiny'
所有外键约束都已更新为新表名,但它们的名称仍基于旧表名。例如,现在我有FK_tblOldAndBusted_tblTastyData,但我想要FK_tblNewAndShiny_tblTastyData。是否有简单的方法来脚本化这个过程?
另外,我是不是太严格了?我知道数据库在约束中使用旧表名也可以正常工作,但感觉像破窗效应。
我在数据库中重命名了一个表,使用的语句是
EXEC sp_rename 'tblOldAndBusted', 'tblNewAndShiny'
所有外键约束都已更新为新表名,但它们的名称仍基于旧表名。例如,现在我有FK_tblOldAndBusted_tblTastyData,但我想要FK_tblNewAndShiny_tblTastyData。是否有简单的方法来脚本化这个过程?
另外,我是不是太严格了?我知道数据库在约束中使用旧表名也可以正常工作,但感觉像破窗效应。
尝试:
exec sp_rename 'FK_tblOldAndBusted_tblTastyData', 'FK_tblNewAndShiny_tblTastyData', 'object'
此外,在处理非默认 schema
时重命名此类事物存在错误。
sp_rename
更改带有点号的名称(例如由EntityFramework创建的约束),则需要在@objname
参数加上方括号,并在@objtype
参数中包含'OBJECT'
:exec sp_rename @objname = '[dbo].[FK_dbo.TableA_dbo.TableB_ColumnOne]', @newname = 'FK_dbo.TableA_dbo.TableC_ColumnOne', @objtype = 'object';
- dperishexec sp_rename 'schema.OldName', 'NewName', 'object'
- pasx在进一步查找后,我发现它实际上必须是这种形式:
EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint', N'OBJECT'
DECLARE @SQLCmd varchar(MAX) = ''
SELECT
@SQLCmd += 'EXEC sp_rename ''' + dc.name + ''', ''DF' +
OBJECT_NAME( dc.parent_object_id ) + c.name + ''', ''OBJECT'';'
FROM
sys.default_constraints dc
JOIN sys.columns c
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE
dc.name != 'DF' + object_name( dc.parent_object_id ) + c.name
AND OBJECT_NAME( dc.parent_object_id ) != 'dtproperties'
EXEC( @SqlCmd )
如果有人感兴趣,我刚刚不得不为名为"EnteredDate"的审计字段重命名所有默认约束条件,以特定的模式进行更新和替换。希望这可以帮助并成为一个起点。
DECLARE @TableName VARCHAR(255), @ConstraintName VARCHAR(255)
DECLARE constraint_cursor CURSOR
FOR
select b.name, c.name from
sys.all_columns a
inner join
sys.tables b
on
a.object_id = b.object_id
inner join
sys.default_constraints c
on a.default_object_id = c.object_id
where
b.name <> 'sysdiagrams'
and a.name = 'EnteredDate' -- column name
and b.type = 'U'
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SqlScript VARCHAR(255) = ''
SET @SqlScript = 'sp_rename ' + @ConstraintName + ', ''DF_' + @TableName + '_EnteredDate'', ''object'''
EXEC(@SqlScript)
SELECT @TableName, @ConstraintName, 'DF_' + @TableName + '_EnteredDate', @SqlScript
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName
END
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;
exec sp_rename 'schema.oldname','newname', 'object'
在原名称前加上模式。
不要在新名称前加上模式...
--
-- Generates a script to rename defaults to the pattern DF_tablename_columnname
--
DECLARE @TableName VARCHAR(255), @ConstraintName VARCHAR(255), @ColumnName varchar(255), @SchemaName varchar(255)
DECLARE constraint_cursor CURSOR
FOR
select b.name, c.name, a.name, sc.name
from sys.all_columns a
inner join sys.tables b on a.object_id = b.object_id
join sys.schemas sc on b.schema_id = sc.schema_id
inner join sys.default_constraints c on a.default_object_id = c.object_id
where
b.name <> 'sysdiagrams'
and b.type = 'U'
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName, @ColumnName, @SchemaName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SqlScript VARCHAR(255) = ''
SET @SqlScript = 'sp_rename ''' + @SchemaName + '.' + @ConstraintName + ''', ''' + @SchemaName + '.DF_' + @TableName + '_' + @ColumnName + ''', ''object''' + char(13) + char(10) + 'GO' + char(13) + char(10)
--EXEC(@SqlScript)
print @sqlscript
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName, @ColumnName, @SchemaName
END
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;
我知道这个帖子有点过时了,但我想发布我的替代方案,以取代@foxfire的答案,因为我对它进行了一些修改。我将其改为只使用较小的名称块,因为我遇到了数据库,其中重命名太多,导致@sql被截断。我还添加了错误处理来退出,以及用于处理不同模式(而非dbo)的模式名称。我选择不使用begin try,以便它可以在多个SQL Server版本中使用。where子句可以操纵以满足OP的原始意图。
BEGIN TRAN
DECLARE @sql varchar(MAX) = '...'
WHILE LEN(@sql) > 0 BEGIN
SET @sql = '';
SELECT TOP 50 @sql = @sql
+ 'EXEC sp_rename N''' + SCHEMA_NAME(dc.[schema_id]) + '.' + dc.name
+ ''', N''DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name
+ ''', ''OBJECT'';' + CHAR(10)
FROM sys.default_constraints dc
inner join sys.columns c
ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
WHERE dc.name LIKE 'DF[_][_]%' -- rename any auto named defaults
PRINT @sql
EXEC(@sql)
IF @@ERROR <> 0 BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRAN
BREAK;
END
END
IF @@TRANCOUNT > 0 COMMIT TRAN
--IF @@TRANCOUNT > 0 ROLLBACK TRAN