CODEWITHSUNDEEP
sqlsql-server
Patrick Schaefer
Patrick Schaefer

Reputation: 851

Select column names that cannot be null

I have several tables with 30+ columns each and I would like to easily get the names of the columns that do not allow for null values.

Is there a simple query that can do this for a table?

Something like describe [table_name] but that only shows required columns, and not necessarily other info about the columns (like type) although that could be nice too.

Upvotes: 1

Views: 3053

Answers (3)

Devart
Devart

Reputation: 122032

USE [dbtest]
GO

CREATE TABLE dbo.[Event]
(
    ID INT PRIMARY KEY,
    Name VARCHAR(10) NULL
)
GO

------------------------------------------------

USE [dbtest] --- !!!
GO

SELECT name
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.Event', 'U')
    AND is_nullable = 0

Output -

name
-----------
ID

Upvotes: 4

Steve Ford
Steve Ford

Reputation: 7763

use the sys.tables and sys.columns virtual tables:

select T.name as [TableName], C.name As [ColumnName]
from sys.columns c
inner join sys.tables t
    on t.object_id = c.object_id
WHERE t.name = 'MyTable' AND c.is_nullable = 0 AND t.type = 'U'

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28940

SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'test1'
    AND is_nullable = 'no'

Upvotes: 1

Related Questions