MSSQL:alter列通常不为null

在Microsoft SQL Server 2008 R2中，我想将可为null的列更改为非null。显然，我可以通过将数据类型重新设置为来实现这一点

alter table t alter column c int not null

例如，如果t.c列的数据类型为int。但是，一般来说，在不重新声明现有数据类型的情况下呢？我正在寻找类似的产品

alter table t alter column c not null

其中现有的数据类型保持不变，并且只有可空性被关闭。

背景

我对我的数据库进行了审计，发现许多情况下，列被指定为可以为null，但在实践中没有null值。我想加强架构，禁止在这些列中使用null值。手动将DDL写入每个DDL的"alter column"很容易出错，因为我可能会得到错误的数据类型。我可以通过使用模式转储程序自动生成代码，该程序输出每列的现有数据类型，但如果转储程序不知道最新的数据类型并输出其他内容（例如，假设它不知道datetime2，而是写出datetime），这也有风险。

SQL服务器已经知道列类型是什么，所以肯定有一种方法可以告诉它保持这种状态，只需关闭可为null的功能。

如果除了找到现有的数据类型将其放入DDL之外，真的没有办法做到这一点，也许你可以推荐一个合适的工具来使用？我从Sybase时代就知道dbschema.pl，但可能还有更现代的东西，或者一些众所周知的SQL片段，可以从模式视图中打印出必要的语句。

两种方法：

1）对这个答案进行扩展，包括对max_length、精度、小数位数和排序名称的考虑。如果您有多个模式，那么您也需要适应这些模式。

SELECT
  'ALTER TABLE '
    +QUOTENAME(aud.[table_name])
    +' ALTER COLUMN '
    +QUOTENAME(aud.[column_name])
    +TYPE_NAME([system_type_id])
    +' NOT NULL;'
FROM MyColumnAuditList aud
INNER JOIN sys.columns col ON (
  col.[object_id] = OBJECT_ID(aud.[table_name]) AND
  col.[name] = aud.[column_name]
)

2）在SSMS中，右键单击数据库并选择"将数据库编写为"。使用您选择的文本分析工具从结果中提取列定义。

Anon提出的"两种方法"答案很有帮助。网站的评论框不允许有足够的文本，所以我将在这里发布我的最终答案。

链接的答案对用户数据类型有特殊的规定，而我的数据库没有，所以我使用type_name内置。此查询尝试对每列的类型进行逆向工程：

select t.name,
       c.name,
       case
         when type_name(c.system_type_id) in (
             'int', 'real', 'float', 'date', 'time', 'datetime', 'datetime2',
             'tinyint', 'smallint', 'smalldatetime', 'bit', 'bigint', 'timestamp',
             'image'
           ) then type_name(c.system_type_id)
         else type_name(c.system_type_id) + '('
           + case
               when precision = 0 then convert(varchar(10), c.max_length)
               else convert(varchar(10), precision) + ', ' + convert(varchar(10), scale)
             end
           + ')'
         end as ty
from sys.tables t
join sys.columns c
  on t.object_id = c.object_id
where c.is_nullable = 1
and   c.is_computed = 0
and   t.schema_id = 1
order by t.name,
         c.name

然后，您可以从该查询中提取每一行，并在运行"alter table"之前检查是否不存在null。我正在做以下事情：

select case when
  exists (select 0 from TABLE)
  and not exists (select 0 from TABLE tablesample (1000 rows) where COLUMN is null)
then 1 else 0 end

对于第一个查询返回的每个TABLE、COLUMN。如果第二个查询返回1，那么您可能可以更改"altertable"。我使用上面的tablesample来防止这对数据库来说太重，因为我计划定期运行检查；如果sp_spaceused返回的表的大小小于100 KB，那么我将省略tablesample子句。

或者，如果你觉得勇敢，你可以运行所有的"alter table"语句，如果列确实包含null，就让它们失败。

奇怪的是，我没有在数据库上的权限在ManagementStudio中右键单击并"将数据库脚本化为"，尽管我可以对单个对象执行此操作。

相关内容

最新更新

热门标签：