Geek Answers Handbook

Search all null columns in a SQL 2000 database

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

+4
source share
2 answers

I don't have sql on hand, but the query looks something like this:

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

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

+8
source

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

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 between schemas or table directories, you should include these fields in the connection, as shown in the answers here:

Differentiation of tables and views in INFORMATION_SCHEMA.COLUMNS .

0
source

More articles:


All Articles