更改 SQL Server 中架构绑定视图引用的列的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1320152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing the size of a column referenced by a schema-bound view in SQL Server
提问by Staelen
I'm trying to change the size of a column in sql server using:
我正在尝试使用以下方法更改 sql server 中列的大小：
ALTER TABLE [dbo].[Address]
ALTER COLUMN [Addr1] [nvarchar](80) NULL
where the length of Addr1was originally 40.
的长度Addr1原来是40.
It failed, raising this error:
它失败了，引发了这个错误：
The object 'Address_e' is dependent on column 'Addr1'.
ALTER TABLE ALTER COLUMN Addr1 failed because one or more objects access
this column.
I've tried to read up on it and it seems that because some views are referencing this column and it seems that SQL Server is actually trying to drop the column that raised the error.
我试图阅读它，似乎是因为某些视图正在引用此列，而且 SQL Server 似乎实际上是在尝试删除引发错误的列。
Address_eis a view created by the previous DB Administrator.
Address_e是前任数据库管理员创建的视图。
Is there any other way I can change the size of the column?
有没有其他方法可以更改列的大小？
采纳答案by Remus Rusanu
The views are probably created using the WITH SCHEMABINDING option and this means they are explicitly wired up to prevent such changes. Looks like the schemabinding worked and prevented you from breaking those views, lucky day, heh? Contact your database administrator and ask him to do the change, after it asserts the impact on the database.
视图可能是使用 WITH SCHEMABINDING 选项创建的，这意味着它们被显式连接以防止此类更改。看起来模式绑定起作用并阻止您破坏这些视图，幸运的一天，呵呵？在断言对数据库的影响后，请联系您的数据库管理员并请他进行更改。
From MSDN:
从MSDN：
SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or droppedto remove dependencies on the table that is to be modified.
架构绑定
将视图绑定到一个或多个基础表的架构。当指定了 SCHEMABINDING 时，不能以影响视图定义的方式修改一个或多个基表。必须首先修改或删除视图定义本身以移除对要修改的表的依赖关系。
回答by Syed Baqar Hassan
ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)
回答by Devshish
If anyone wants to "Increase the column width of the replicated table" in SQL Server 2008, then no need to change the property of "replicate_ddl=1". Simply follow below steps --
如果有人想在 SQL Server 2008 中“增加复制表的列宽”，则无需更改“ replicate_ddl=1”的属性。只需按照以下步骤操作——
- Open SSMS
- Connect to Publisher database
- run command --
ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22) - It will increase the column width from
varchar(x)tovarchar(22)and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replication
- 打开 SSMS
- 连接到发布者数据库
- 运行命令——
ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22) - 它将增加列宽从
varchar(x)tovarchar(22)和您可以在订阅者上看到的相同更改（交易被复制）。所以不需要重新初始化复制
Hope this will help all who are looking for it.
希望这会帮助所有正在寻找它的人。
回答by RRUZ
See this link
看这个链接
Resize or Modify a MS SQL Server Table Column with Default Constraint using T-SQL Commands
使用 T-SQL 命令调整或修改具有默认约束的 MS SQL Server 表列的大小
the solution for such a SQL Server problem is going to be
此类 SQL Server 问题的解决方案将是
Dropping or disabling the DEFAULT Constraint on the table column.
删除或禁用表列上的 DEFAULT 约束。
Modifying the table column data type and/or data size.
修改表列数据类型和/或数据大小。
Re-creating or enabling the default constraint back on the sql table column.
在 sql 表列上重新创建或启用默认约束。
Bye
再见
回答by Passingn Searcher
here is what works with the version of the program that I'm using: may work for you too.
这是适用于我正在使用的程序版本的内容：可能也适用于您。
I will just place the instruction and command that does it. class is the name of the table. you change it in the table its self with this method. not just the return on the search process.
我只会放置执行它的指令和命令。class 是表的名称。您可以使用此方法在表中自行更改它。不仅仅是搜索过程的回报。
view the table class
查看表类
select * from class
change the length of the columns FacID (seen as "faci") and classnumber (seen as "classnu") to fit the whole labels.
更改列 FacID（视为“faci”）和 classnumber（视为“classnu”）的长度以适合整个标签。
alter table class modify facid varchar (5);
alter table class modify classnumber varchar(11);
view table again to see the difference
再次查看表格以查看差异
select * from class;
(run the command again to see the difference)
（再次运行命令以查看差异）
This changes the the actual table for good, but for better.
这会永远改变实际的表，但会更好。
P.S. I made these instructions up as a note for the commands. This is not a test, but can help on one :)
PS 我把这些说明作为命令的注释。这不是一个测试，但可以帮助一个:)
回答by user3103989
Check the column collation. This script might change the collation to the table default. Add the current collation to the script.
检查列排序规则。此脚本可能会将排序规则更改为表默认值。将当前排序规则添加到脚本中。
