• MS SQL SERVER 创建表、索引、添加字段等常用脚本


    创建表:

    if not exists ( select 1 from sysobjects where id=object_id('PayChannelNm') )
    create table [dbo].PayChannelNm(
        [Id] bigint IDENTITY(1,1) NOT NULL,
        PayChannel [varchar](20) not null,
        PayChannelName nvarchar(50) not null,
        Memo nvarchar(200)  null,
        Seq bigint not null constraint df_PayChannelNm_Seq default 0,
        IsHide bit not null constraint df_PayChannelNm_IsHide default 0,
        CreateTime datetime not null constraint df_PayChannelNm_CreateTime default  getdate(),
        UpdateTime datetime not null constraint df_PayChannelNm_UpdateTime default  getdate(),
        CONSTRAINT [PK_PayChannelNm_Id] PRIMARY KEY NONCLUSTERED 
        (
            [Id] ASC
        )
    )  
    go

    单列唯一索引

    if not exists (select * from sysindexes where name = 'idx_PayChannelNm_unique1') 
    begin
        create unique index idx_PayChannelNm_unique1 on PayChannelNm (PayChannel asc)
    end
    go

    多列唯一索引

    if not exists (select * from sysindexes where name = 'idx_QrOrder_unique1') 
    begin
        create unique index idx_QrOrder_unique1 on QrOrder (mchno,out_trade_no asc)
    end
    go

    查询索引

    if not exists (select * from sysindexes where name = 'idx_QrOrder_Notify') 
    begin
        create unique index idx_QrOrder_Notify on QrOrder (third_mchno,out_trade_no,create_time asc)
    end
    go

    给表添加字段

    if not exists ( select 1 from syscolumns where id = object_id('QrOrder') and name = 'qr_template_id')
     alter table QrOrder add qr_template_id nvarchar(32) 
    go

    添加不可空字段

    if not exists ( select 1 from syscolumns where id = object_id('QrOrder') and name = 'third_mchno')
     alter table QrOrder add third_mchno nvarchar(30) not null constraint df_QrOrder_third_mchno default('0')
    go

    调整字段长度

    alter table PayStore alter column tl_channel_code varchar(32)

  • 相关阅读:
    微软校园招聘  研发工程师A
    版本号排序
    腾讯2018校园招聘  研发工程师笔试题(三)
    好词好句
    HikariCP 连接最快的连接池
    Ubuntu 18.04.1 安装java8
    Ubuntu 18.04.1 安装mysql 5.7.27
    markdown 插入链接
    面试题 int(3) int(10) 区别
    采购单(京东2017秋招真题)
  • 原文地址:https://www.cnblogs.com/runliuv/p/15513850.html
Copyright © 2020-2023  润新知