SQL，如何更改 SQL 表中的列而不破坏其他依赖关系？
我确信这可能是很常见的查询，但目前找不到好的答案。
这是我的问题：
我有一个名为 Contacts 的表，其中包含 varchar 列 Title。现在在开发过程中，我想将字段 Title 替换为 TitleID，它是 ContactTitles 表的外键。目前表 Contacts 有 60 多个依赖项（其他表、视图函数）。
我怎样才能以最安全、最简单的方式做到这一点？
我们使用：MSSQL 2005，数据已经迁移，只是想更改架构。
编辑：
感谢大家的快速重播。
正如前面提到的，Contacts 表有超过 60 个依赖项，但是当运行以下查询时，其中只有 5 个使用 Title 列。迁移脚本已运行，因此无需更改数据。
<代码> /*获取所有使用指定列的对象*/
> <代码> 选择名称 FROM 系统注释 sc JOIN sysobjects so ON sc.id = so.id 其中“%Title%”等文本和“%TitleID%”等文本
然后我浏览了这 5 个视图并手动更新了它们。
I'm sure this might be quite common query but couldn't find good answer as for now.
Here is my question:
I've got a table named Contacts with varchar column Title. Now in the middle of development I want to replace field Title with TitleID which is foreign key to ContactTitles table. At the moment table Contacts has over 60 dependencies (other tables, views functions).
How can I do that the safest and easiest way?
We use: MSSQL 2005, data has already been migrated, just want to change schema.
Edit:
Thanks to All for quick replay.
Like it was mentioned Contacts table has over 60 dependents, but when following query was run, only 5 of them use Title column. Migration script was run, so no data changes required.
/*gets all objects which use specified column */
SELECT Name
FROM syscomments sc
JOIN sysobjects so ON sc.id = so.id
WHERE TEXT LIKE '%Title%' AND TEXT LIKE '%TitleID%'
Then I went through those 5 views and updated them manually.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用重构方法。首先创建一个名为
TitleID的新字段，然后将所有标题复制到ContactTitles表中。然后，一一更新每个依赖项以使用 TitleID 字段。只需确保每个步骤后您仍然拥有一个可以运行的系统即可。如果数据将发生变化，您必须小心并确保对
Title列的任何更改也会更改ContactTitles表。您只需在进行重构时保持它们同步即可。编辑：甚至有一本书是关于它的！ 重构数据库。
Use refactoring methods. Start off by creating a new field called
TitleID, then copy all the titles into theContactTitlestable. Then, one by one, update each of the dependencies to use the TitleID field. Just make sure you've still got a working system after each step.If the data is going to be changing, you'll have to be careful and make sure that any changes to the
Titlecolumn also change theContactTitlestable. You'll only have to keep them in sync while you're doing the refactoring.Edit: There's even a book about it! Refactoring Databases.
正如其他人指出的那样，这取决于您的 RDBMS。
有两种方法：
As others pointed out it depends on your RDBMS.
There are two approaches:
对于 Microsoft SQL Server Redgate 有一个（非免费）产品可以帮助进行此重构 http://www.red-gate.com/products/sql_refactor/index.htm
在过去，我通过简单地获取要审查的内容列表
（并且可能是考虑依赖关系信息并按对象类型进行过滤）
在 Management Studio 中编写所有感兴趣的脚本，然后简单地查看列表并查看所有内容并将 CREATE 更改为 ALTER。即使对于 60 个可能的依赖项，这也应该是一个非常简单且重复的更改。此外，如果您引用不存在的列，则在运行脚本进行 ALTER 时应该会收到错误消息。
如果您在查询中使用
*或在应用程序中使用即席 SQL，显然事情可能会更困难一些。For Microsoft SQL Server Redgate have a (not free) product that can help with this refactoring http://www.red-gate.com/products/sql_refactor/index.htm
In the past I have managed to do this quite easily (if primitively) by simply getting a list of things to review
(and possibly taking dependencies information into account and filtering by object type)
Scripting all the ones of interest in Management Studio then simply going down the list and reviewing them all and changing the CREATE to ALTER. It should be quite a simple and repetitive change even for 60 possible dependencies. Additionally if you are referring to a non existent column you should get an error message when you run the script to ALTER.
If you use
*in your queries or adhoc SQL in your applications obviously things may be a bit more difficult.使用 SP_Depend '表名称' 检查表的依赖关系
然后使用 SP_Rename 重命名列名，这非常有用。
每当重命名 PRIMARY KEY 或 UNIQUE 约束时，sp_rename 都会自动重命名关联的索引。如果重命名的索引与 PRIMARY KEY 约束相关联，则 sp_rename 也会自动重命名 PRIMARY KEY 约束。
然后开始一一更新过程和函数，没有其他好的选择可以像这样进行更改，如果您发现也请告诉我。
Use SP_Depend 'Table Name' to check the Dependencies of the table
and then Use the SP_Rename to Rename the Column Name which is very useful.
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.
and then start Updating the Procedure and Functions one by one there is no other good option for change like this if you found then tell me too.