SQL常用数据库结构升级语句

青旅半醒 2022-02-02 14:53 304阅读 0赞

修改视图
    --SQL Server 2005 
    GO
    IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[e_myWorkflowProcessModule]'))
    DROP VIEW [dbo].[e_myWorkflowProcessModule]
    GO
    
    CREATE VIEW [dbo].[e_myWorkflowProcessModule]    
    AS 
    
    --------
    GO

![复制代码][copycode.gif]

修改存储过程
    --SQL2008\SQL2005
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOtherFamilyByMainCst]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[GetOtherFamilyByMainCst]
    GO
    
    CREATE PROC [dbo].[GetOtherFamilyByMainCst]
    (@familyguid uniqueidentifier)
    AS
    BEGIN
    
    END 
    GO

![复制代码][copycode.gif]

修改表结构
    1创建表
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Bid_Publish]') AND type in (N'U'))
    DROP TABLE [dbo].[Bid_Publish]
    GO
    CREATE TABLE [dbo].[Bid_Publish](
        [HtTypeGUID] [uniqueidentifier] ,
        [BUGUID] [uniqueidentifier] NOT NULL,
        [HtTypeShortCode] [varchar](10) NULL,
        [HtTypeCode] [varchar](100) NULL,
        [HtTypeShortName] [varchar](40) NULL,
        [HtTypeName] [varchar](400) NULL,
        [ParentCode] [varchar](100) NULL,
        [Level] [tinyint] NULL  ,
        [IfEnd] [tinyint] NULL ,
        [AlterWarnRate] [money] ,
        [PayWarnRate] [money] ,
        [CostGUID] [uniqueidentifier] ,
        [FinanceHsxmCode] [varchar](20) ,
        [FinanceHsxmName] [varchar](60) ,
        [Remarks] [text] NULL,
     CONSTRAINT [PK__cb_HtType_History] PRIMARY KEY CLUSTERED 
    (
        [HtTypeGUID] ASC
    )
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

![复制代码][copycode.gif]

2修改表字段
    GO
     IF NOT EXISTS ( SELECT  *
                    FROM    syscolumns
                    WHERE   name = 'ApplyAmountBak_Bz'
                            AND id = OBJECT_ID('cb_HTFKApply') ) 
        BEGIN
       alter table [cb_HTFKApply] add  
       [ApplyAmountBak_Bz] [money];
        END
     GO 
     
     IF NOT EXISTS ( SELECT  *
                    FROM    syscolumns
                    WHERE   name = 'ApplySPAmount_Bz'
                            AND id = OBJECT_ID('cb_HTFKApply') ) 
        BEGIN
       alter table [cb_HTFKApply] add  
       [ApplySPAmount_Bz] [money];
        END
     
     GO

![复制代码][copycode.gif]

创建函数，用指定分隔符将指定字符串分开后返回一个表
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[SplitID]
    GO
    
    CREATE FUNCTION [dbo].[SplitID]
    (@String NVARCHAR (4000), @SplitChar NVARCHAR (10))
    RETURNS 
        @table TABLE (
            [ID] VARCHAR (100) NULL)
    AS
    BEGIN  
       DECLARE  @Index  INT  
       SET  @Index  =  0  
    
            IF @String <> ''
            Begin
                IF RIGHT(@String,1)<> @SplitChar 
                    SET @String = @String + @SplitChar
                IF LEFT(@String,1)= @SplitChar 
                SET @String = STUFF(@String, 1, 1, '')
            End
     
           WHILE  CHARINDEX(@SplitChar,@String,@Index)  >  0    
           BEGIN  
               INSERT INTO @table(ID)
                   VALUES (SUBSTRING(@String, @Index, CHARINDEX(@SplitChar, @String,
                    @Index) - @Index))
                   SET @index = CHARINDEX(@SplitChar, @String, @Index) + 1 END  
    RETURN  
    END
    
    
    GO

![复制代码][copycode.gif]

SQL Server2005开始，TOP子句后可以跟常量或者变量,跟常量时可省略括号,即top(2)和top 2是等价的(注意不加括号时top和2间的空格),top后使用SQL变量时必须使用括号,例： 
    Sql代码
    declare @num int   
    set @num = 10    
    select top(@num) * from sys.tables

本文转自欢醉博客园博客，原文链接http://www.cnblogs.com/zhangs1986/p/3365615.html如需转载请自行联系原作者

欢醉

[copycode.gif]: /images/20220202/770490260ef54a44aa088d7cabc55151.png