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)

 

。