Finding all Nullable Columns in SQL 2000 Database

Question

How to find out column with NULL values allowed in the insert in whole database ?

You may / may not also wish to exclude the ones that can't be made null b/c of a check constraint. which may lead to this: https://stackoverflow.com/a/3195136/420667, Substring, and CharIndex can be used to get the column name from the definition. — user420667, Jun 14 '19 at 01:22

Jhonny D. Cano -Leftware- · Accepted Answer · 2009-07-11T12:06:42.330

14

I don't have sql at hand, but the query goes something like this

  SELECT * FROM information_schema.columns WHERE is_nullable = 'YES'

In general, search for this stardard view, for all the metadata info about your schema and structure of the database; there are many others (information_schema.tables, information_schema.constraints, etc)

edited Jul 11 '09 at 12:06

answered Jul 11 '09 at 11:21

Jhonny D. Cano -Leftware-

17,663
14
81
103

Probably should be is_nullable = 'YES' (caps) to avoid issues if your database is set to be case sensitive. – Rob Levine Jul 11 '09 at 11:28

score 1 · Answer 2 · answered Sep 14 '17 at 16:51

Those who only want to see columns from base tables (not views) should join with INFORMATION_SCHEMA.TABLES. I also like to exclude the system table sysdiagrams.

Query

SELECT
     c.TABLE_NAME,
     COLUMN_NAME,
     DATA_TYPE
FROM
     INFORMATION_SCHEMA.COLUMNS AS c
     JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME
WHERE
     is_nullable = 'YES' AND
     TABLE_TYPE = 'BASE TABLE' AND
     c.TABLE_NAME != 'sysdiagrams'
ORDER BY
     c.TABLE_NAME,
     COLUMN_NAME

If you have duplicate table names across schemas or table catalogs, you should involve those fields in the join as well, as shown in the answers here:

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS.

Finding all Nullable Columns in SQL 2000 Database

2 Answers2