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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.