How to find the name of not-null constraints in SQL Server

How can I find the name of a named not-null constraint in SQL Server? I can find check constraints, default constraints, FK constraints, PK constraints and unique constraints, but the NN constraint has eluded me.

Answers


You can't.

Whilst the syntax does accept a name...

CREATE TABLE T
(
C INT CONSTRAINT NN NOT NULL
)

... and it is parsed and validated as a name ...

CREATE TABLE T
(
C INT CONSTRAINT NN123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789 NOT NULL
)
/* The identifier that starts with 'NN1234...6' is too long. Maximum length is 128. */

... this is then ignored and isn't actually stored anywhere.

No row is added to sys.objects for these unlike other constraints. It is just stored as a bit property of the associated column rather than a constraint object.


List columns with not-null constraint

DECLARE @Table NVARCHAR(MAX) = 'Your table name here'

SELECT
    [COLUMN_NAME] AS [Column]
    --, [DATA_TYPE] AS [Type]
FROM
    [INFORMATION_SCHEMA].[COLUMNS]
WHERE
    [IS_NULLABLE] = 'NO'
    AND (@Table = '' OR [TABLE_NAME] = @Table)
ORDER BY 
    [TABLE_NAME]

Notes

  • Replace Your table name here on line 1 at the top of the query, with the name of the table you need a list for.

  • If you also need to know the data type of each column, uncomment line 4 by deleting the two dashes at the start of the line.


You are able to query from the information _schema all columns that are nullable. Does that get you what you want?

SELECT 
  table_catalog, table_schema, table_name, column_name, is_nullable

  FROM information_schema.columns 
  WHERE table_name = 'users'

Need Your Help

MVC 4 \ cshtml compile location

asp.net asp.net-mvc-4 extjs razor

Just to make sure I get the architecture right:

Apple Watch`s Activity app - draw circles?

ios objective-c swift watchkit

I want to draw circles like in apple`s activity app on apple watch. Researching about it I find that many do this by just providing an Image for each state of the circle. There is software who help...