In generic terms, you use the ALTER TABLE command followed by the table name, then the MODIFY command followed by the column name and new type and size. Here is an example: ALTER TABLE tablename MODIFY columnname VARCHAR(20) ; The maximum width of the column is determined by the number in parentheses.
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
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.
ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)
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.
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
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 :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With