Stack Overflow
Stack Overflow

Questions tagged [alter-column]

Alter column is a part of sql's Alter table statement used to change an existing column in a table.

Alter column can be used to set a default value, changing the column's data type, or changing the column's maximum length.

Alter column is supported in Microsoft Access and in Sql Server. To modify columns properties in Oracle, for example, you will need to use Modify column instead.

Alter column related tags

62 questions
2
votes
3 answers

Change Auto-Increment, Primary Key field from SMALLINT to INT in SQL Server

What is the best way (low impact/low risk) to change a primary key field from SMALLINT to INT? The field is configured to use "Identity Increment" to auto-increment. I'm starting with the following SQL: ALTER TABLE category_types ALTER COLUMN id…
user2578229
  • 145
  • 2
  • 7
2
votes
2 answers

Remove the collation of a column in SQL Server

How can I remove the collation of a column after it has been set? The standard collation of a column in the SQL Server is "NULL". It means, the collation of the database is used. The TSQL-Statement ALTER table ALTER column COLLATE database_default…
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37
1
vote
2 answers

how to set maximum length to int data type in sql

ALTER TABLE table_name ALTER COLUMN column_name [int] (4) NULL; unable to execute the script, please assist how can I add maximum length to already existed column for an int data type.
K Kumar
  • 131
  • 5
  • 14
1
vote
2 answers

POSTGRESQL convert date to unix timestamp int

I have a table matches with a column deleted_date which is of type date, so its values look like 2020-12-30 right now. I want to change the type of this column from date to int so I can store unix timestamps instead. I am trying to do so by running…
Martin
  • 1,336
  • 4
  • 32
  • 69
1
vote
2 answers

SQL Server: Double data size of a table when altering column type

I altered the type of a column in to an actual smaller type: -- change type from nvarchar(100) to varchar(50) Alter Table [MyTable] Alter column [MyColumn] varchar(50) The table contains 4 Mio. records. Before it used around 1.1 GB of space, after…
Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
1
vote
1 answer

HSQLDB (HyperSQL): Changing column type in a TEXT table

For the CsvCruncher project, I am loading a CSV file into HSQLDB. CREATE TEXT TABLE concat_1 ( Op VARCHAR(255), id VARCHAR(255), uuid VARCHAR(255), session_id VARCHAR(255) ) SET TABLE concat_1 SOURCE…
Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277
1
vote
1 answer

TSQL: How are values converted when altering a numeric column in sql server?

This is on SQL Server 2008. I have several columns I want to convert from money and decimal to varchar, for example a column called item_amount. How will these values be converted? Will it be the same as convert(varchar, item_amount)? Running a…
Factor Mystic
  • 26,279
  • 16
  • 79
  • 95
1
vote
3 answers

ALTER COLUMN when table depends on the column

I'm trying to alter a column type to increase its size but SQL gives me the following message Msg 5074, Level 16, State 1, Line 1 The object 'table_name' is dependent on column 'column_name'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER…
Luiz Eduardo Simões
  • 146
  • 1
  • 11
1
vote
1 answer

Same data type while using liquibase modify data type

I was wondering Is there any preconditions run by default for liquibase modifydatatype upgrade? For example if i am changing column "A" whose data type is integer in following way :
Rohit
  • 13
  • 2
1
vote
3 answers

Dynamic T-SQL - Alter column definition to max length of field

I am trying to dynamically create code to alter the column definition to the max length of the field. Note that the contents of the database will not change. Here is what I have so far, but I cannot separately execute the max length query to get a…
Danny Rancher
  • 1,923
  • 3
  • 24
  • 43
1
vote
3 answers

MS Access - sql expression for allow null?

I use MS Access (2003) database. Once I create a column I set NOT NULL using sql statement: ALTER TABLE Table1 ALTER column myColumn INTEGER not null Is there a way to change it back to allow null values? I already tried: ALTER TABLE Table1…
Tracer
  • 2,544
  • 2
  • 23
  • 58
1
vote
2 answers

Why does my table file size double after altering a column?

After I changed the varchar limit on a column from 512 to max, in SQL Server 2008 R2, I noticed the file size of the table had more than doubled. I didn't expect it to increase this much, but since I was increasing it I thought it made sense. So I…
Ben Harrison
  • 2,121
  • 4
  • 24
  • 40
1
vote
1 answer

Reduce decimal data type precision & scale and avoid arithmetic overflow error

I have a field in a SQL Server 2012 database that is currently decimal(30,20) because that is the format of the data provider. However, that large of a decimal is completely unnecessary and would like to trim it down to something like decimal(16,7)…
Ricketts
  • 5,025
  • 4
  • 35
  • 48
0
votes
3 answers

Need to Alter data type in a column common to all tables in a schema

I need to alter the Data Type of a named column (let's call them 'photo') across all the tables in a schema. For example: I have 300 tables with identical structure (except the data in each row of course). But I have to change the varchar(255) in…
fulgore
  • 11
  • 1
0
votes
0 answers

How to change/alter the data type of a column from a table which has a custom data type in POSTGRESQL?

I have a custom datatype as below. CREATE TYPE myschema.test AS ( id text, event text, severity text, status text, value text, text text, type text, update_time timestamp without time…
MichaelCorSibin
  • 1
1
2
3 4 5