MsSQLserver中修改字段值系统自动生成的脚本

主要参考一下脚本的实现思路:①创建临时表②创建临时表③向临时表插入数据④删除原表⑤临时表改名

/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_VBILL_HEAD
(
ID nvarchar(36) NOT NULL,
TENANCY_ID nvarchar(6) NULL,
VESSEL_VISIT_ID nvarchar(36) NOT NULL,
I_SHIP_NO nvarchar(36) NULL,
E_SHIP_NO nvarchar(36) NULL,
I_E_ID nvarchar(4) NOT NULL,
BILL_NO nvarchar(40) NOT NULL,
BILL_TYPE nvarchar(6) NOT NULL,
ORDER_NO nvarchar(24) NULL,
TRADE_ID nvarchar(4) NULL,
HANG_PLACE_COD nvarchar(10) NULL,
CUSTOM_COD nvarchar(10) NULL,
LINE_ID nvarchar(10) NULL,
FEE_CLIENT_COD nvarchar(15) NULL,
SHIPPER nvarchar(4000) NULL,
CONSIGNEE nvarchar(4000) NULL,
NOTIFY nvarchar(4000) NULL,
SHIPPER_ADDR nvarchar(200) NULL,
CONSIGNEE_ADDR nvarchar(200) NULL,
NOTIFY_ADDR nvarchar(200) NULL,
PAYER nvarchar(10) NULL,
PAYER_PLACE nvarchar(80) NULL,
DELV_TERM nvarchar(24) NULL,
LOADING_TIME ntext NULL,
DISCHING_TIME ntext NULL,
TRANS_FLAG nchar(1) NOT NULL,
TRANS_TYPE nvarchar(6) NULL,
LCL_FLAG nchar(1) NOT NULL,
LCL_NO nvarchar(40) NULL,
SOC_FLAG nchar(1) NULL,
EMPTY_FLAG nchar(1) NULL,
CUSTOM_STATE nvarchar(10) NULL,
LOAD_TYPE nvarchar(10) NULL,
PRE_VESSEL_NAME nvarchar(80) NULL,
PRE_VESSLE_VOY nvarchar(10) NULL,
LOAD_NOTE nvarchar(100) NULL,
POR nvarchar(5) NULL,
POR_NAME nvarchar(80) NULL,
POS nvarchar(5) NULL,
POS_NAME nvarchar(80) NULL,
POL nvarchar(5) NULL,
POL_NAME nvarchar(80) NULL,
POD nvarchar(5) NULL,
POD_NAME nvarchar(80) NULL,
PODEST nvarchar(5) NULL,
PODEST_NAME nvarchar(80) NULL,
POT nvarchar(5) NULL,
POT_NAME nvarchar(80) NULL,
SEQ_NO numeric(11, 0) NULL,
QUANTITY numeric(11, 0) NULL,
CNTR_OPER_ID nvarchar(10) NULL,
RECEIVER nvarchar(100) NULL,
SENDER nvarchar(50) NULL,
FORWARDER nvarchar(50) NULL,
SPOD nvarchar(5) NULL,
CARGO_ID nvarchar(35) NULL,
RELEASE_FLAG nchar(1) NOT NULL,
EXIT_CUSTOM_FLAG nchar(1) NOT NULL,
FEE_ID nchar(1) NOT NULL,
PRE_VOYAGE_NO nvarchar(36) NULL,
TRANS_VOYAGE_NO nvarchar(36) NULL,
PHASE nvarchar(6) NULL,
GOODS_NOTES nvarchar(255) NULL,
PIECES numeric(14, 4) NULL,
WEIGHT numeric(14, 4) NULL,
VOLUME numeric(14, 4) NULL,
MARKS nvarchar(60) NULL,
PKG nvarchar(10) NULL,
NOTES nvarchar(1000) NULL,
CREATED_BY nvarchar(36) NULL,
CREATED_ON date NULL,
UPDATED_BY nvarchar(36) NULL,
UPDATED_ON datetime NULL,
RELEASE_WAY nvarchar(60) NULL,
RELEASE_NOTES nvarchar(200) NULL,
LOCK_FLAG nchar(1) NULL,
LOCK_MAN nvarchar(36) NULL,
LOCK_DTE ntext NULL,
LOCK_NOTES nvarchar(200) NULL,
NOTICE_FLAG nchar(1) NULL,
NOTICE_FAX nvarchar(50) NULL,
NOTICE_TEL nvarchar(50) NULL,
NOTICE_DTE ntext NULL,
DELIVERY_MAN nvarchar(36) NULL,
DELIVERY_DTE ntext NULL,
DELIVERY_NOTES nvarchar(200) NULL,
DELIVERY_FLAG nchar(1) NULL,
SIGN_FLAG nchar(1) NULL,
SIGN_MAN nvarchar(36) NULL,
SIGN_DTE ntext NULL,
SIGN_NOTES nvarchar(200) NULL,
CARRY_FLAG nchar(1) NULL,
ATTRI_FLAG nchar(1) NULL,
ATTRI_NEXT_FLAG nchar(1) NULL,
LD_TERM nvarchar(24) NULL,
TRADE_TERM nvarchar(24) NULL,
QUARANTINE_TERM nvarchar(24) NULL,
SIGN_POS nvarchar(5) NULL,
SIGN_POS_NAME nvarchar(80) NULL,
FEE_CONTRACT_NO nvarchar(50) NULL
)  ON [PRIMARY]
 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_VBILL_HEAD SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.VBILL_HEAD)
 EXEC('INSERT INTO dbo.Tmp_VBILL_HEAD (ID, TENANCY_ID, VESSEL_VISIT_ID, I_SHIP_NO, E_SHIP_NO, I_E_ID, BILL_NO, BILL_TYPE, ORDER_NO, TRADE_ID, HANG_PLACE_COD, CUSTOM_COD, LINE_ID, FEE_CLIENT_COD, SHIPPER, CONSIGNEE, NOTIFY, SHIPPER_ADDR, CONSIGNEE_ADDR, NOTIFY_ADDR, PAYER, PAYER_PLACE, DELV_TERM, LOADING_TIME, DISCHING_TIME, TRANS_FLAG, TRANS_TYPE, LCL_FLAG, LCL_NO, SOC_FLAG, EMPTY_FLAG, CUSTOM_STATE, LOAD_TYPE, PRE_VESSEL_NAME, PRE_VESSLE_VOY, LOAD_NOTE, POR, POR_NAME, POS, POS_NAME, POL, POL_NAME, POD, POD_NAME, PODEST, PODEST_NAME, POT, POT_NAME, SEQ_NO, QUANTITY, CNTR_OPER_ID, RECEIVER, SENDER, FORWARDER, SPOD, CARGO_ID, RELEASE_FLAG, EXIT_CUSTOM_FLAG, FEE_ID, PRE_VOYAGE_NO, TRANS_VOYAGE_NO, PHASE, GOODS_NOTES, PIECES, WEIGHT, VOLUME, MARKS, PKG, NOTES, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON, RELEASE_WAY, RELEASE_NOTES, LOCK_FLAG, LOCK_MAN, LOCK_DTE, LOCK_NOTES, NOTICE_FLAG, NOTICE_FAX, NOTICE_TEL, NOTICE_DTE, DELIVERY_MAN, DELIVERY_DTE, DELIVERY_NOTES, DELIVERY_FLAG, SIGN_FLAG, SIGN_MAN, SIGN_DTE, SIGN_NOTES, CARRY_FLAG, ATTRI_FLAG, ATTRI_NEXT_FLAG, LD_TERM, TRADE_TERM, QUARANTINE_TERM, SIGN_POS, SIGN_POS_NAME, FEE_CONTRACT_NO)
SELECT ID, TENANCY_ID, VESSEL_VISIT_ID, I_SHIP_NO, E_SHIP_NO, I_E_ID, BILL_NO, BILL_TYPE, ORDER_NO, TRADE_ID, HANG_PLACE_COD, CUSTOM_COD, LINE_ID, FEE_CLIENT_COD, SHIPPER, CONSIGNEE, NOTIFY, SHIPPER_ADDR, CONSIGNEE_ADDR, NOTIFY_ADDR, PAYER, PAYER_PLACE, DELV_TERM, LOADING_TIME, DISCHING_TIME, TRANS_FLAG, TRANS_TYPE, LCL_FLAG, LCL_NO, SOC_FLAG, EMPTY_FLAG, CUSTOM_STATE, LOAD_TYPE, PRE_VESSEL_NAME, PRE_VESSLE_VOY, LOAD_NOTE, POR, POR_NAME, POS, POS_NAME, POL, POL_NAME, POD, POD_NAME, PODEST, PODEST_NAME, POT, POT_NAME, SEQ_NO, QUANTITY, CNTR_OPER_ID, RECEIVER, SENDER, FORWARDER, SPOD, CARGO_ID, RELEASE_FLAG, EXIT_CUSTOM_FLAG, FEE_ID, PRE_VOYAGE_NO, TRANS_VOYAGE_NO, PHASE, GOODS_NOTES, PIECES, WEIGHT, VOLUME, MARKS, PKG, NOTES, CREATED_BY, CONVERT(date, CREATED_ON), UPDATED_BY, UPDATED_ON, RELEASE_WAY, RELEASE_NOTES, LOCK_FLAG, LOCK_MAN, LOCK_DTE, LOCK_NOTES, NOTICE_FLAG, NOTICE_FAX, NOTICE_TEL, NOTICE_DTE, DELIVERY_MAN, DELIVERY_DTE, DELIVERY_NOTES, DELIVERY_FLAG, SIGN_FLAG, SIGN_MAN, SIGN_DTE, SIGN_NOTES, CARRY_FLAG, ATTRI_FLAG, ATTRI_NEXT_FLAG, LD_TERM, TRADE_TERM, QUARANTINE_TERM, SIGN_POS, SIGN_POS_NAME, FEE_CONTRACT_NO FROM dbo.VBILL_HEAD WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.VBILL_HEAD
GO
EXECUTE sp_rename N'dbo.Tmp_VBILL_HEAD', N'VBILL_HEAD', 'OBJECT' 
GO
COMMIT
select Has_Perms_By_Name(N'dbo.VBILL_HEAD', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.VBILL_HEAD', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.VBILL_HEAD', 'Object', 'CONTROL') as Contr_Per 

来自为知笔记(Wiz)