Reputation: 851
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: 3051
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
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
Reputation: 28930
SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'test1'
AND is_nullable = 'no'
Upvotes: 1