Link to home
Start Free TrialLog in
Avatar of midfde
midfdeFlag for United States of America

asked on

How do I change a table column from NULL to NOT NULL by T-SQL

Because of some historic reasons I want to be able to modify a column like:
ALTER TABLE [t] ALTER COLUMN [c] NOT NULL
Transact SQL does not allow this syntax: I think it wants data type after the column name.
Is there a way to say "Change this column to NOT NULL whatever type it is"?
Could you please help me to complete a store procedure (See code) without inquiring INFORMATION_SCHEMA?

PS: Example.
alter table [Anomaly Entries] alter column [anomalyID] varchar(15) not null -- works
alter table [Anomaly Entries] alter column [anomalyID]                    not null -- does not

create proc change_to_not_null(@tn as sysname, @cn as sysname)as 
exec ('alter table [' + @tn + ']alter column ' + @cn +<???> + ' NOT NULL')
go

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial