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