This question popped up on the MSDN forum today: Status of BIT type columns

Here is a way to quickly find all the columns in your database which allow NULLS

T-SQL
1
2
3
4
select table_name,column_name
from information_schema.columns
where is_nullable ='YES'
order by table_name,column_name
select table_name,column_name
from information_schema.columns
where is_nullable ='YES'
order by table_name,column_name

Now you might have noticed that some of these are views. You can join with information_schema.tables and filter on table_type = ‘base table’ to list just the tables.

T-SQL
1
2
3
4
5
6
select c.table_name,c.column_name,t.table_type
from information_schema.columns c 
join information_schema.tables t on c.table_name = t.table_name
where is_nullable ='YES'
and table_type = 'base table'
order by table_name,column_name
select c.table_name,c.column_name,t.table_type
from information_schema.columns c 
join information_schema.tables t on c.table_name = t.table_name
where is_nullable ='YES'
and table_type = 'base table'
order by table_name,column_name

To list all the columns in your database regardless if they are in a view or a table and if they allow NULLS or not use this

T-SQL
1
2
3
select table_name,column_name,is_nullable
 from information_schema.columns
order by table_name,column_name
select table_name,column_name,is_nullable
 from information_schema.columns
order by table_name,column_name

You can also find this in our SQL Server Admin Hacks wiki

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum