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