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'.

Solution 1

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

Solution 2

ALTER TABLE table_name ALTER COLUMN column_name datatype

Comments

[Jerameel Resco]

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