如何确定字段是否设置为非null？

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.

我部署了一个现有程序,其中一些客户数据库的字段设置为非空,而其他客户数据库可以为空。我需要运行一个补丁来纠正数据库,以便列可以为空,但不需要针对所有数据库运行它,只需要对它不正确。是否有一个可以在SQL Server中执行此检查的简单方法?最好是可以作为SQL脚本的一部分运行的东西。

3 个解决方案

#1

Look into the INFORMATION_SCHEMA views. For example:

查看INFORMATION_SCHEMA视图。例如:

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".

IS_NULLABLE将为“YES”或“NO”。

#2

select Table_Name, Column_Name, Is_Nullable
from information_schema.columns

Will get you that info

会得到你的信息

#3

select isnullable from syscolumns where name = 'status'

#1