Recreate tables without dropping

Asked

Viewed 441 times

2

I have an already populated table. In it there is a field not null, which I need to set to null. In graphical mode does not accept and says I have to recreate the table. Is there a way to do this without me having to drop the table and still keep the data? Is there any way to do this?

sql sql-server

  • What message does it give?

  • It must have a method of copying the data from the table and saving them in an external file, so you can dropar and recreate the table...

2 answers

6


Run a query to change the table:

ALTER TABLE [table-name] ALTER COLUMN [column-name] [type] NULL

  • You solved Luis Henrique, but why in design mode I can’t? I don’t understand.

  • Honestly in this case do not know why in Transact-sql it allows. The design will lock because it is one of 4 actions not allowed when the protection mode is activated. https://msdn.microsoft.com/en-us/library/bb895146.aspx

  • The design should allow this action, since changing from not-null to null does not cause any side effects. That is why I am in favour of validations to be made at application level, database only to store them even.

5

Unfortunately not. But you can in: Ferramentas > Opções > Designers Then uncheck the option: Evitar salvas alterações que exijam recriação de tabela

This way Sql Manager will create a new temporia table, move the data to it, drop the old one and rename the temporaria to the definitive name.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.