Finding all Nullable Columns in SQL 2000 Database

Question

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

Answer 1

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)

Answer 2

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.