SQL Server ->> 生成代码把表字段从NULL改为NOT NULL

一般我们用SELECT .... INTO语句生成的表字段都是允许为NULL。而如果我们需要改成NOT NULL呢

select 'ALTER TABLE dbo.XXXXXXX ALTER COLUMN ' + 
QUOTENAME(c.name) + ' ' + t.name + 
case when t.name in ('nvarchar','nchar') 
        then '('+ cast(c.max_length/2 as nvarchar) +')' 
    when t.name in ('varchar','binary', 'char', 'varbinary') 
        then '('+ cast(c.max_length as nvarchar) +')' 
else '' end + ' NOT NULL'
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id 
where object_id = object_id ('dbo.XXXXXXX') and t.name <> 'sysname'
order by column_id 

 

有时需要准备数据库设计文档，里面的物理设计这块需要填写像这样的表

Table: dbo.DimMonth
Column Name	Data Type	Key	Nullable	Comment
Monthkey	bigint	PK	No
CalendarMonth	bigint		No
CalendarMonthName	nvarchar(30)		No
CalendarQuarter	bigint		No
CalendarQuarterName	nvarchar(30)		No
CalendarYear	bigint		No
CalendarYearName	nvarchar(30)		No
FiscalMonthName	nvarchar(30)		No
FiscalMonthOfYear	bigint		No
FiscalQuarter	bigint		No
FiscalQuarterName	nvarchar(30)		No
FiscalYear	bigint		No
FiscalYearName	nvarchar(30)		No

 

如果比本身已经建立好在数据库中，我们可以通过一条脚本生成

select c.name, t.name + case when t.name in ('nvarchar','nchar') 
        then '('+ cast(c.max_length/2 as nvarchar) +')' 
    when t.name in ('varchar','binary', 'char', 'varbinary') 
        then '('+ cast(c.max_length as nvarchar) +')' 
else '' end, case when c.is_nullable = 1 then 'Yes' ELSE 'No' end
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id 
where object_id = object_id ('dbo.XXXXXXX') and t.name <> 'sysname'
order by column_id