Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MSSQL: editing / dropping columns fail

igitur's profile image igitur posted 4 years ago in General Permalink
Currently, the script to edit an existing column on a table fails. The current statement that is executed is:
ALTER TABLE "dbo"."T2" ALTER COLUMN  "id" BIGINT NULL DEFAULT NULL;
and this is invalid on MSSQL.

The statement should be only:
ALTER TABLE "dbo"."T2" ALTER COLUMN  "id" BIGINT NULL;
and the default value for the column should be done separately

Default values:
- First check whether there are existing default values on the column.
select [name] from sys.objects where type = 'D' and parent_object_id = object_id('T2')

- If existing default values exist, drop them first:
alter table dbo.T2 drop constraint DF__T2__id__38996AB5

- Then add a new default value for the column:
alter table dbo.T3 add default 'default_value' for name


When dropping a column, the existing default value constraints should be dropped too, like described above. Else the dropping of the column fails too.

Please login to leave a reply, or register at first.