CODEWITHSUNDEEP
sqlsql-servernullconstraints
KuldipMCA
KuldipMCA

Reputation: 3149

Finding all Nullable Columns in SQL 2000 Database

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

Upvotes: 8

Views: 1336

Answers (2)

MarredCheese
MarredCheese

Reputation: 20811

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.

Upvotes: 1

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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)

Upvotes: 14

Related Questions