在不破坏脚本和存储过程的情况下重命名列

2022-01-01 00:00:00 sql rename sql-server triggers

我想将列名修改为表中存在的新名称

I want to modify a column name to new name present in a table

但是这里的问题我想手动修改 Triggers 或 SP's 中存在的 column name.

but here problem i want to manually modify the column name present in Triggers or SP's.

有没有更好的方法.

要重命名一列正在使用这个

sp_RENAME 'Tablename.old_Column', 'new_column' , 'COLUMN';

类似地，我如何为 triggers 或 SP's 做到这一点?不打开每个脚本?

similarly how can i do it for triggers or SP's.? without opening each script?

推荐答案

嗯，有很多 3rd 方工具承诺这种类型的安全重命名"，有些是免费的，有些不是:

Well, there are a bunch of 3rd party tools that are promising this type of "safe rename", some for free and some are not:

  • ApexSQL 有一个免费工具，正如 MWillemse 在他的回答中所写，
  • RedGate 有一个名为 SQLPrompt 的商业工具，它也有一个安全的重命名功能，但它远非免费.
  • Microsoft 有一个名为 SQL Server 数据工具(或简称 SSDT)，正如 Dan Guzman 所写在他的评论中.
  • ApexSQL has a free tool for that, as MWillemse wrote in his answer,
  • RedGate have a commercial tool called SQLPrompt that also have a safe renaming feture, However it is far from being free.
  • Microsoft have a visual studio add-in called SQL Server Data Tools (or SSDT in the short version), as Dan Guzman wrote in his comment.

我不得不说我从来没有尝试过这些特定工​​具中的任何一个来完成特定任务，但我确实对 SSDT 和 RedGate 的一些产品有一些经验，我认为它们是非常好的工具.我对 ApexSQL 一无所知.

I have to say I've never tried any of these specific tools for that specific task, but I do have some experience with SSDT and some of RedGate's products and I consider them to be very good tools. I know nothing about ApexSQL.

另一种选择是尝试自己编写 sql 脚本，但是在开始之前需要考虑以下几点:

Another option is to try and write the sql script yourself, However there are a couple of things to take into consideration before you start:

  • 您的表可以直接从 sql server 外部访问吗?我的意思是，是否有可能某些软件直接在该表上执行 sql 语句?如果是这样，您可能会在重命名该列时破坏它，并且在这种情况下没有 sql 工具会有所帮助.
  • 你的 sql 脚本技能真的有那么好吗?我认为自己对 sql server 相当有经验，但我认为编写这样的脚本超出了我的技能.并不是说这对我来说是不可能的，但对于我可以免费获得的东西，可能需要花费太多时间和精力.

如果您决定自己编写，有几篇文章可能会帮助您完成该任务:

Should you decide to write it yourself, there are a few articles that might help you in that task:

首先，sys.sql_expression_dependencies的微软官方文档.
二、一篇文章叫Different Ways查找由 13 年经验 DBA 编写的 SQL Server 对象依赖项，最后但并非最不重要的是，相关问题在 StackExchange 的数据库管理员网站上.

First, Microsoft official documentation of sys.sql_expression_dependencies.
Second, an article called Different Ways to Find SQL Server Object Dependencies that is written by a 13 years experience DBA, and last but not least, a related question on StackExchange's Database Administrator's website.

当然，您可以采用 Gordon Linoff 在他的评论中建议的安全方式，或者使用他的回答中建议的目的地数据等同义词，但是您将不得不手动修改所有列依赖项，并且从我的理解，这就是你想要避免的.

You could, of course, go with the safe way Gordon Linoff suggested in his comment, or use synonyms like destination-data suggested in his answer, but then you will have to manually modify all of the columns dependencies manually, and from what I understand, that is what you want to avoid.

相关文章