一般来说，MSSQL：alter column not null-Java 学习之路

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

alter table t alter column c int not null

例如，如果列t.c是int数据类型 . 但总的来说，没有重述现有的数据类型呢？我正在寻找一些相当于

alter table t alter column c not null

保持现有数据类型的位置，并且仅关闭可为空性 .

Background

我已经对我的数据库进行了审计，发现很多情况下列被指定为可为空但在实践中没有出现空值 . 我想收紧模式以禁止这些列中的空值 . 手动将DDL写入每个“更改列”是容易出错的，因为我可能会错误地获取数据类型 . 我可以使用模式转储器程序自动生成代码，该程序输出每列的现有数据类型，但是如果转储程序不知道最新的数据类型并输出其他内容（例如，假设它）也存在风险不知道datetime2并写出日期时间而不是） .

SQL服务器已经知道列类型是什么，所以肯定有一种方法可以告诉它保持它并且只是关闭可空性 .

如果除了通过找到现有数据类型将其放入DDL之外真的没办法，或许你可以推荐一个合适的工具来使用它？我从Sybase时代了解dbschema.pl，但可能会有更现代的东西，或者一些着名的SQL片段，它从架构视图中打印出必要的语句 .

2 回答

1
两种方法：

1）展开this answer以包括对max_length，precision，scale和collation_name的考虑 . 如果您有多个模式，那么您也需要适应这种模式 .
```
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中，右键单击数据库并选择“脚本数据库为” . 使用您选择的文本解析工具从结果中提取列定义 .
回复于 2024-04-26T09:20:59+08:00
0
Anon建议的“两种方法”答案很有帮助 . 该网站的评论框不允许足够的文字，所以我会在这里发布我的最终答案 .

linked answer对我的数据库没有的用户数据类型有特殊规定，所以我使用的是 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”之前检查是否存在空值 . 我正在做类似以下的事情：
```
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，那么您可以更改“alter table” . 我使用上面的tableample来阻止这个数据库太沉重，因为我打算定期运行检查;如果sp_spaceused返回的表的大小小于100千字节，那么我省略了tablesample子句 .

或者，如果你觉得勇敢，你可以运行所有'alter table'语句，如果列确实包含空值，则让它们失败 .

奇怪的是，我没有权限在数据库中右键单击Management Studio和“脚本数据库为”，虽然我可以为单个对象执行此操作 .
回复于 2024-04-26T09:20:59+08:00

一般来说，MSSQL：alter column not null

2 回答

相关问题