SQL Server：我如何检查一个字段是否有一个“NULL”或“NOT NULL”约束到位？ [英] SQL Server: How can I check to see if a field has a "NULL" or "NOT NULL" constraint in place?

问题描述

任何人都可以让我知道，如果有一种方法，以编程方式确定Micorosft SQL Server数据库表字段是否有NULL或NOT NULL约束到位？我需要这样，以便我可以部署一个安全可重新运行的补丁。所以我是这样的（概念/伪代码）：

Could anyone please let me know if there is a way to programatically determine if a Micorosft SQL Server database table field has a NULL or NOT NULL constraint in place? I need this so that I can deploy a patch that is safe to be re-runnable. So I'm after something like this (conceptual/pseudo-code):

IF（my_table COLUMN end_date HAS CONSTRAINT OF'NOT NULL' ALTER TABLE my_table ALTER COLUMN end_date DATETIME NULL

所以我想将my_table.end_date从'NOT NULL'改为'NULL'已经改变。我只是不确定括号中的部分应该是什么。

So I want to change my_table.end_date from 'NOT NULL' to 'NULL' if it hasn't already been changed. I'm just unsure of what the part in the brackets should be.

我知道如何询问dbo.sysobjects的东西，像现有的字段，现有外键约束和like（有几个线程已经在那），但我只是不知道如何专门检查一个NULL / NOT NULL字段约束。

I know how to interrogate dbo.sysobjects for things like existing fields, existing foreign key constraints and the like (there are a few threads already on that), but I'm just not sure how to check specifically for a NULL/NOT NULL field constraint. Any help would be much appreciated.

推荐答案

您可以查看 INFORMATION_SCHEMA.COLUMNS ：

if (select IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME='my_table' and COLUMN_NAME='end_date') = 'NO' 
begin
    ALTER TABLE my_table ALTER COLUMN end_date DATETIME NULL
end

这篇关于SQL Server：我如何检查一个字段是否有一个“NULL”或“NOT NULL”约束到位？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！