I have a column in my DB which is currently defined as NOT NULL. I would like to update this column to allow NULLs.
I have the following script to do this however I would like to check first if the column is already NULL (or NOT NULL), as it may have been changed previously.
ALTER TABLE [dbo].[aud] ALTER COLUMN [actname] nvarchar(50) NULL Any help appreciated.
Use COLUMNPROPERTY to get column property . You may write something like
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.aud'),'actname','AllowsNull') AS 'AllowsNull'; For more information please visit this link
sys tables? Not that efficiency will be a massive overhead in this scenario.
select is_nullable from sys.columns c inner join sys.tables t on t.object_id = c.object_id where t.name = 'aud' and c.name = 'actname' Will give you a BIT representing whether it is nullable or not.
So you could switch on this like
IF EXISTS(SELECT * from sys.columns c inner join sys.tables t on t.object_id = c.object_id where t.name = 'aud' and c.name = 'actname' AND is_nullable = 1) BEGIN --What to do if nullable END ELSE BEGIN --What to do if not nullable END END That of course assumes that the table and column exist at all...
There isn't really a need to do that, because if it's already Nullable, changing a column from Nullable to Nullable will have no negative effect.
However you can do it with this query:
SELECT is_nullable FROM sys.columns WHERE object_id=object_id('YourTable') AND name = 'yourColumn' 
