在不破坏脚本和存储过程的情况下重命名列 [英] Renaming a column without breaking the scripts and stored procedures

问题描述

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

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.

这篇关于在不破坏脚本和存储过程的情况下重命名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！