views:

141

answers:

3

I have an existing program deployed where some customer databases have a field set to not null, while others are nullable. I need to run a patch to correct the database so that the column is nullable but do not need to run it against all databases, just ones where it is incorrect. Is there a simple method that can be used in SQL Server to perform this check? Preferably something that can be run as part of a SQL script.

+2  A: 
select Table_Name, Column_Name, Is_Nullable
from information_schema.columns

Will get you that info

cmsjr
Can you comment on why information_schema.columns is better than syscolumns. I honestly don't know, not being antagonistic.
Joshua Belden
Absolutely, I would say, where possible to use them, the schema views are preferable because 1. They are less susceptible to change than the system tables (e.g. sys.objects vs sysobjects) and 2. They tend to aggregate system table info helpfully (e.g. foreign key references)
cmsjr
Perfect, thank you.
Joshua Belden
Another important point is that they are in the SQL-92 ANSI standard. The sys.objects, etc. tables are Microsoft specific.
Tom H.
I was unaware of that, thanks for the info.
cmsjr
+2  A: 
select isnullable from syscolumns where name = 'status'
Joshua Belden
This query needs to have extra where clause items as if there are multiple tables with status columns you will get multiple results.
Mitchel Sellers
+9  A: 

Look into the INFORMATION_SCHEMA views. For example:

SELECT
     IS_NULLABLE
FROM
     My_DB.INFORMATION_SCHEMA.COLUMNS
WHERE
     TABLE_SCHEMA = 'dbo' AND
     TABLE_NAME = 'My_Table' AND
     COLUMN_NAME = 'My_Column'

IS_NULLABLE will be either "YES" or "NO".

Tom H.