SQL Server->> generates code to change table fields from NULL to NOT NULL

Generally we use SELECT .... The table fields that are generated by the into statement are allowed to be null. And if we need to change to NOT NULL?

Select 'ALTER TABLE dbo. XXXXXXX ALTER COLUMN' + QUOTENAME(c.name)+ ' ' +T.name+  Case  whenT.nameinch('nvarchar','nchar')          Then '('+ cast(C.max_length/2  as nvarchar)+')'      whenT.nameinch('varchar','binary','Char','varbinary')          Then '('+ cast(C.max_length as nvarchar)+')' Else "' End + 'Not NULL' fromSys.columns CJoinSys.types T onc.system_type_id=t.system_type_idwhere object_id = object_id('dbo. XXXXXXX') andT.name<> 'sysname'Order  bycolumn_id

Sometimes you need to prepare a database design document, the physical design of this piece needs to fill out a table like this

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

If the database is better than itself, we can generate it through a script

SelectC.name, T.name+  Case  whenT.nameinch('nvarchar','nchar')          Then '('+ cast(C.max_length/2  as nvarchar)+')'      whenT.nameinch('varchar','binary','Char','varbinary')          Then '('+ cast(C.max_length as nvarchar)+')' Else "' End, Case  whenC.is_nullable= 1  Then 'Yes' ELSE 'No' End fromSys.columns CJoinSys.types T onc.system_type_id=t.system_type_idwhere object_id = object_id('dbo. XXXXXXX') andT.name<> 'sysname'Order  bycolumn_id

SQL Server->> generates code to change table fields from NULL to NOT NULL