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
2009-04-30 17:55:16
Can you comment on why information_schema.columns is better than syscolumns. I honestly don't know, not being antagonistic.
Joshua Belden
2009-04-30 17:56:40
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
2009-04-30 18:03:47
Perfect, thank you.
Joshua Belden
2009-04-30 18:06:53
Another important point is that they are in the SQL-92 ANSI standard. The sys.objects, etc. tables are Microsoft specific.
Tom H.
2009-04-30 19:21:55
I was unaware of that, thanks for the info.
cmsjr
2009-04-30 20:09:59
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
2009-04-30 18:18:40
+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.
2009-04-30 17:55:58