重命名表SQL Server，通过PK和FK级联更改

I want to find a sql command or something that can do this where I have a table named tblFoo and I want to name it tblFooBar. However, I want the primary key to also be change, for example, currently it is:

我想找到一个sql命令或者可以执行此操作的地方,我有一个名为tblFoo的表,我想将其命名为tblFooBar。但是,我希望主键也可以更改,例如,目前它是:

CONSTRAINT [PK_tblFoo] PRIMARY KEY CLUSTERED

And I want a name change to change it to:

我想要更改名称以将其更改为:

CONSTRAINT [PK_tblFooBar] PRIMARY KEY CLUSTERED

Then, recursively go through and cascade this change on all tables that have a foreigh key relationship, eg. from this:

然后,递归地遍历并在具有外部密钥关系的所有表上级联此更改,例如。由此:

CHECK ADD  CONSTRAINT [FK_tblContent_tblFoo] FOREIGN KEY([fooID])

To this:

 CHECK ADD  CONSTRAINT [FK_tblContent_tblFooBar] FOREIGN KEY([fooID])

Naturally, I am trying not to go through and do this all manually because a) it is an error prone process, and b)it doesn't scale.

当然,我试图不通过手动完成这一切,因为a)这是一个容易出错的过程,而b)它不能扩展。

4 个解决方案

#1

This is just off the top of my head and isn't complete (you'd need to add similar code for indexes). Also, you would need to either add code to avoid renaming objects from a table with the same base name, but additional characters - for example, this code would also list tblFoo2 and all of its associated objects. Hopefully it's a start for you though.

这只是我的头脑,并不完整(你需要为索引添加类似的代码)。此外,您需要添加代码以避免重命名具有相同基本名称的表中的对象,但需要添加其他字符 - 例如,此代码还会列出tblFoo2及其所有关联对象。希望这对你来说是一个开始。

DECLARE
    @old_name   VARCHAR(100),
    @new_name   VARCHAR(100)

SET @old_name = 'tblFoo'
SET @new_name = 'tblFooBar'

SELECT
    'EXEC sp_rename ''' + name + ''', ''' + REPLACE(name, @old_name, @new_name) + ''''
FROM dbo.sysobjects
WHERE name LIKE '%' + @old_name + '%'

#2

Good answer by Tom
I've just extended his query here to include indexes

汤姆的好答案我刚刚在这里扩展他的查询以包括索引

declare
 @old nvarchar(100),
 @new nvarchar(100)

set @old = 'OldName'
set @new = 'NewName'

select 'EXEC sp_rename ''' + name + ''', ''' + 
  REPLACE(name, @old, @new) + ''''
 from sys.objects 
 where name like '%' + @old + '%'
union -- index renames
select 'EXEC sp_rename ''' + (sys.objects.name + '.' + sys.indexes.name)  +  ''', ''' +
  REPLACE(sys.indexes.name, @old, @new) + ''', ''INDEX'''
  from sys.objects 
   left join sys.indexes on sys.objects.object_id = sys.indexes.object_id
  where sys.indexes.name like '%' + @old + '%'

#3

A great tool that takes the pain out of renaming tables is Red Gate SQL Refactor It will automatically find your dependency's and work all that stuff out for you too.

Red Gate SQL Refactor是一个很好的工具,可以解决重命名表的问题。它会自动找到你的依赖项,并为你完成所有这些工作。

Big fan :-)

粉丝 :-)

#4

SQL Server won't do this directly as far as I am aware. You would have to manually build the script to do the change. This can be achieved by generating the SQL for the table definition (SSMS will do this) and doing a search and replace on the names.

据我所知,SQL Server不会直接执行此操作。您必须手动构建脚本才能进行更改。这可以通过为表定义生成SQL(SSMS将执行此操作)并对名称进行搜索和替换来实现。

#1

DECLARE
    @old_name   VARCHAR(100),
    @new_name   VARCHAR(100)

SET @old_name = 'tblFoo'
SET @new_name = 'tblFooBar'

SELECT
    'EXEC sp_rename ''' + name + ''', ''' + REPLACE(name, @old_name, @new_name) + ''''
FROM dbo.sysobjects
WHERE name LIKE '%' + @old_name + '%'

#2