一般来说，MSSQL：alter column not null(MSSQL: alter column not null, in general)

一般来说，MSSQL：alter column not null(MSSQL: alter column not null, in general)

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

alter table t alter column c int not null

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

alter table t alter column c not null

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

背景

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

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

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

In Microsoft SQL Server 2008 R2, I would like to change a nullable column to not null. Obviously, I could do this by restating the datatype, as

alter table t alter column c int not null

if the column t.c is of int datatype, for example. But what about in general, without restating the existing data type? I'm looking for some equivalent of

alter table t alter column c not null

where the existing datatype is kept in place, and only the nullability is turned off.

Background

I have done an audit of my database and found many cases where a column is specified as nullable but no null values occur in practice. I'd like to tighten up the schema to forbid null values in these columns. Manually writing the DDL to 'alter column' for each one is error-prone because I might get the datatype wrong. I could automatically generate the code by using a schema-dumper program which outputs the existing datatype of each column, but that too has risks, if the dumper program is not aware of the latest datatypes and outputs something else (as an example, suppose it doesn't know about datetime2 and writes out datetime instead).

The SQL server already knows what the column type is, so surely there is a way to tell it to keep that and just turn off the nullability.

If there's really no way to do it except by finding the existing datatype to put it into the DDL, perhaps you could recommend a suitable tool to use? I know about dbschema.pl from Sybase days but there might be something more modern, or some well-known fragment of SQL that prints out the necessary statements from the schema views.

最满意答案

两种方法：

1）扩展此答案以包括对max_length，precision，scale和collat​​ion_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中，右键单击数据库并选择“脚本数据库为”。 使用您选择的文本解析工具从结果中提取列定义。

The 'two approaches' answer suggested by Anon is helpful. The website's comment box doesn't allow enough text so I will post my final answer here.

The linked answer has special provision for user data types, which my database doesn't have, so I am using the type_name builtin instead. This query tries to reverse-engineer the type for each column:

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

Then you can take each row from this query and do a check that no nulls exist before running 'alter table'. I am doing something like the following:

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

for each TABLE, COLUMN returned by the first query. If the second query returns 1 then you can probably make the 'alter table' change. I use tablesample above to stop this being too heavy on the database, since I plan to run the check regularly; if the size of the table as returned by sp_spaceused is less than 100 kilobytes then I omit the tablesample clause.

Or, if you feel brave you could just run all the 'alter table' statements and let them fail if the column does contain nulls.

Oddly, I don't have permissions on the database to right-click in Management Studio and 'script database as', although I can do it for individual objects.