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?
This question is related to
sql
sql-server
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 --
ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22)
varchar(x)
to varchar(22)
and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replicationHope this will help all who are looking for it.
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
ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)
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 :)
Check the column collation. This script might change the collation to the table default. Add the current collation to the script.
Source: Stackoverflow.com