重命名列而不破坏脚本和存储过程 [英] Renaming a column without breaking the scripts and stored procedures
问题描述
我想将列名修改为表中存在的新名称
I want to modify a column name to new name present in a table
但是在这里，我想手动修改列名出现在触发器或 SP的中。
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';
类似地，我如何为触发器做它或 SP的。？
similarly how can i do it for triggers or SP's.? without opening each script?
推荐答案
嗯，有很多第三方工具都承诺使用这种类型的安全重命名 ，有些是免费的，有些则不是：
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 。
第二，称为找到SQL Server对象依赖关系的不同方法由拥有13年经验的DBA，
和最后但并非最不重要的人，。
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屋！
