How to change a column values from nchar(28) to nchar(45)

Category: sql server tools

Question

EdCal on Mon, 28 Jan 2013 23:17:02


Using Sql Server Management Studio, how do I change a column from nchar(28) to nchar(45).  When I attempted to do so I receive message:  You must recreate to make changes.   The table is empty so I will not lose any data, but how to I recreate the table using the Sql Server Management Studio?  Help.  Need instructions because this is the first table I have created using this tool.


ecb

Replies

Alberto Morillo on Mon, 28 Jan 2013 23:50:53


Hello,

Please see the following article:

http://www.sqlcoffee.com/Troubleshooting074.htm  

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

TiborK on Tue, 29 Jan 2013 06:50:15


I suggest you do this using ALTER TABLE ... ALTER COLUMN instead of using the GUI (which will create a new table...).

Satheesh Variath on Tue, 29 Jan 2013 07:00:31


Just un-check the  option in Tools>Options>Designers>  "Prevent saving changes that require table re-creation" 

Or

You can use ALTER TABLE ALTER Column to do this by T-SQL

ALTER TABLE <<TABLE NAME>>
ALTER COLUMN <<ColumnName>> <<Datatype>> <<NULL OPtion>>



Regards
Satheesh


Waqas M on Tue, 29 Jan 2013 07:19:11


Hi,

How to I recreate the table using the Sql Server Management Studio?

Answer: For this let the designer made the changes for you, please do the following:

- On SSMS click on the Tools menu, then click Options.
- Click on Designers
- Clear the "Prevent saving changes that require table re-creation" check box.

You can use T-SQL query also for the alter table statement

use <database_name>
alter table <table_name>
alter column <column_name> nchar(45) <null option>



Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

Waqas

MS(SPM), MS(E&F),M.Engg, MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
Twitter: @waqas8777
Linked In: http://www.linkedin.com/in/waqasm1