• 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)

  • 相关阅读:
    CentOs7-替换下载源
    CentOs7-常用命令
    Django Nginx+uwsgi 安装配置
    Linux操作系统下文件作用
    U盘创建macOS安装盘
    国内开源镜像站点汇总
    gcd常见用法
    mac rvm 升级 ruby 安装cocoapod 指定版本
    confluence 搭建 wiki 并破解
    homebrew 安装 java 指定版本
  • 原文地址:https://www.cnblogs.com/runliuv/p/15513850.html
Copyright © 2020-2023  润新知