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

修改视图
--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

修改存储过程
--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

修改表结构
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]

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

创建函数，用指定分隔符将指定字符串分开后返回一个表
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

 

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