How to alter a column's attribute using sql script

Question

How can I alter a column's attribute using a sql script?

Here's what I've tried but I got errors:

ALTER TABLE [dbo].[tblBiometricPattern] COLUMN BiometricPatternID TINYINT NOT NULL IDENTITY(1,1)

Thank you in advance.

Here's the error message that appears when executed:

Incorrect syntax near the keyword 'COLUMN'.

i would seriously question how wise creating an IDENTITY on a tinyint is — Mitch Wheat
– Mitch Wheat, Commented Jan 31, 2011 at 8:14
Identity on a tinyint is a no-no. Basically it's either a really small table, and you're better off managing the ID yourself (to keep consistent across deployments and such), or it's a large table, and then tinyint is just, well, tiny! — SWeko
– SWeko, Commented Jan 31, 2011 at 8:20
It just happen that I just wanted to try it on a different data type. =) — Jerameel Resco
– Jerameel Resco, Commented Jan 31, 2011 at 8:22

Damien_The_Unbeliever · Accepted Answer · 2011-01-31 08:21:48Z

2

If you're trying to alter the column so that it's an IDENTITY column... you can't do that. You can add a new column with the identity property, but you can't alter an existing column.

If that's not what you're trying to do, perhaps you could include the actual error messages you're getting.

The general form for altering an existing column is:

ALTER TABLE [dbo].[tblBiometricPattern] ALTER COLUMN BiometricPatternID TINYINT NOT NULL IDENTITY(1,1)

(that is, you were missing the word "ALTER" before COLUMN). But as I say, this will now return an error telling you that you can't change the IDENTITY property of the column.

If the column is already an identity column, and you're just altering the datatype, then leave off the IDENTITY() property. It will still be an identity column:

ALTER TABLE [dbo].[tblBiometricPattern] ALTER COLUMN BiometricPatternID TINYINT NOT NULL

edited Jan 31, 2011 at 8:21

answered Jan 31, 2011 at 8:15

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerameel Resco Over a year ago

Thanks for the information Damien_The_Unbeliever. I've already included the error message on the SQL.

bsanneh · Accepted Answer · 2011-01-31 09:21:12Z

2

ALTER TABLE table_name ALTER COLUMN column_name datatype

answered Jan 31, 2011 at 9:21

bsanneh

5211 gold badge9 silver badges17 bronze badges

Comments

Saleh Enam Shohag · Accepted Answer · 2018-03-28 18:09:43Z

1

If you want to alter/modify column of a table.
For MySQL / Oracle (prior version 10G):

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

For Oracle 10G and later:

ALTER TABLE table_name MODIFY column_name datatype;

answered Mar 28, 2018 at 18:09

Saleh Enam Shohag

1,1391 gold badge13 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to alter a column's attribute using sql script

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related