Changing the size of a column referenced by a schema-bound view in SQL Server

Question

I m trying to change the size of a column in sql server using   ALTER TABLE  dbo   Address  ALTER COLUMN  Addr1   nvarchar  80  NULL   where the length of Addr1 was originally 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   Address e is a view created by the previous DB Administrator   Is there any other way I can change the size of the column

User · Answer

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.

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.

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 :)

User · Answer

See this link  Resize or Modify a MS SQL Server Table Column with Default Constraint using T-SQL Commands  the solution for such a SQL Server problem is going to be   Dropping or disabling the DEFAULT Constraint on the table column   Modifying the table column data type and or data size   Re-creating or enabling the default constraint back on the sql table column   Bye

User · Answer

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 --   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  to varchar 22  and same change you can see on subscriber  transaction got replicated   So no need to re-initialize the replication   Hope this will help all who are looking for it

User · Answer

ALTER TABLE  table name  ALTER COLUMN  column name  varchar 150

User · Answer

Check the column collation  This script might change the collation to the table default  Add the current collation to the script

User · Answer

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   From 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 dropped to   remove dependencies on the table that   is to be modified

[sql] Changing the size of a column referenced by a schema-bound view in SQL Server

Examples related to sql

Examples related to sql-server