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. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Return all the columns in the database which allow NULLS
From Wiki
Here is a way to quickly find all the columns in your database which allow NULLS
- 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.
- 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
- SELECT table_name,column_name,is_nullable
- FROM information_schema.columns
- ORDER BY table_name,column_name
Contributed by: --SQLDenis 19:33, 14 January 2009 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.