sqlserver 操作数据表语句模板

从网上搜的，一点一点加吧。

-----------设置事务全部回滚-----------------
SET XACT_ABORT ON

BEGIN
BEGIN  TRY
    BEGIN TRANSACTION TR


---------------------------------删除表=======================
--DROP TABLE T_QuestionBankItem


---=================================修改列名========================================
--exec sp_rename 'T_MyCourse.CourseClassId' , 'ClassId', 'column'




-----==========================修改字段类型及默认值------------------

--ALTER TABLE T_Message ALTER COLUMN Title nvarchar(20) NOT NULL




--===========================增加列======================================----

----------------T_AssNews-------------
--alter table T_AssNews ADD ViewCount int NOT NULL Default 0
--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'浏览量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_AssNews', @level2type=N'COLUMN',@level2name=N'ViewCount'



-----------删除字段、先删除约束
--ALTER TABLE [dbo].[T_TICategory] DROP CONSTRAINT [DF_T_TICategory_TrainingInstitutionId]    
--ALTER TABLE T_TICategory DROP COLUMN TrainingInstitutionId



    
     COMMIT TRANSACTION TR
     END TRY

     BEGIN CATCH 
        PRINT 'EXECUTED FAILED';

        select error_number() as error_number ,
             error_message() as error_message
        ROLLBACK TRANSACTION TR
        RETURN
    END CATCH
END

 

转载于:https://www.cnblogs.com/dawenyang/p/9046025.html