CODEWITHSUNDEEP
sqlsql-servert-sql
cdsln
cdsln

Reputation: 898

Finding out if a column is NOT NULL

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.

Upvotes: 14

Views: 13581

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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'

Upvotes: 3

Pawan
Pawan

Reputation: 1075

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

Upvotes: 13

El Ronnoco
El Ronnoco

Reputation: 11912

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

Upvotes: 7

Related Questions