XML FeedsThis 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
tsql
- 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.
tsql
- 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
tsql
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.