Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Return all the columns in the database which allow NULLS

From Wiki

Jump to: navigation, search

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

  1. select table_name,column_name
  2. from information_schema.columns
  3. where is_nullable ='YES'
  4. 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.

  1. select c.table_name,c.column_name,t.table_type
  2. from information_schema.columns c
  3. join information_schema.tables t on c.table_name = t.table_name
  4. where is_nullable ='YES'
  5. and table_type = 'base table'
  6. 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

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

Contributed by: --SQLDenis 19:33, 14 January 2009 (GMT)

Part of SQL Server Admin Hacks

612 Rating: 2.3/5 (36 votes cast)