刷新所有函數、視圖、存儲過程

本文链接：https://blog.csdn.net/steven088/article/details/7174567

/*
刷新函數/視圖/存儲過程
Chelen
2007-02-10
*/
Create Proc sp_RefreshObject
As
Set NoCount On
--定義讀取函數/存儲過程內容變量
declare @SyscomText nvarchar(4000)

--替換Create用
Declare @CurID int, @CurStr Varchar(255), @Pos int, @Start int
Declare @HadAlter Bit, @Note int, @NoteLine Bit

--執行SQL語句用
Declare @Sql1 nVarchar(4000) --存儲要定義的變量字串('@0 nvarchar(4000),@1 nvarchar(4000),...@n nvarchar(4000)')
Declare @Sql2 nVarchar(4000) --存儲初始化變量的字串('@0=N'',@1=N'',...,@n=N''')
Declare @Sql3 nVarchar(4000) --存儲用于計算各變量的Sql語句(@0=CASE g WHEN 0 THEN @0+a ELSE @0 END,@1=CASE g WHEN 1 THEN @1+a ELSE @1 END, ..., @n=Case g When n then @n + a Else @n End)
Declare @Sql4 nVarchar(4000) --存儲變量列表字符串(@0+@1+....+@n)
Declare @i int, @ic nVarchar(10)

--
Declare @objname sysName
Declare @PrevLabel Varchar(1)
Declare @RefCount int

--刷新自定義函數
Select @PrevLabel = 'F', @RefCount = 0
declare cs_Function cursor for
select Name from dbo.sysobjects where xtype in('FN','IF','TF') And status>=0 Order By Crdate
For Read Only
OPEN cs_Function

FETCH NEXT FROM cs_Function INTO @ObjName
WHILE @@FETCH_STATUS = 0
BEGIN
Goto AlterSql
Funlab:
PRINT N'函數' + @ObjName + N'已更新'
Set @RefCount = @RefCount + 1
FETCH NEXT FROM cs_Function INTO @ObjName
END
CLOSE cs_Function
DEALLOCATE cs_Function
Print N'--已更新' + Convert(Varchar,@RefCount) + N'個函數--'
Print ''

--刷新视图
Select @RefCount = 0
DECLARE cs_View CURSOR FOR
SELECT Name from sysobjects WHERE xtype = 'V' And status>=0 order by crdate
FOR READ ONLY
OPEN cs_View

FETCH NEXT FROM cs_View INTO @ObjName
WHILE @@FETCH_STATUS = 0
BEGIN
   exec sp_refreshview @ObjName
   PRINT N'視圖' + @ObjName + N'已更新'
Set @RefCount = @RefCount + 1
   FETCH NEXT FROM cs_View INTO @ObjName
END
CLOSE cs_View
DEALLOCATE cs_View
Print N'--已更新' + Convert(Varchar,@RefCount) + N'個視圖--'
Print ''

--刷新存儲過程
Select @PrevLabel = 'P', @RefCount = 0
declare cs_Proc cursor for
select Name from dbo.sysobjects where xtype='P' And status>=0 Order By Crdate
For Read Only
OPEN cs_Proc

FETCH NEXT FROM cs_Proc INTO @ObjName
WHILE @@FETCH_STATUS = 0
BEGIN
Goto AlterSql
Proclab:
   PRINT N'存儲過程' + @ObjName + N'已更新'
Set @RefCount = @RefCount + 1
   FETCH NEXT FROM cs_Proc INTO @ObjName
END
CLOSE cs_Proc
DEALLOCATE cs_Proc

Print N'--已更新' + Convert(Varchar,@RefCount) + N'個存儲過程--'
Print ''

Print '更新完成!'

Set NoCount Off

Return (0)

AlterSql:
Create Table #(ID int Identity(1,1),
G int Default 0,
a nVarchar(4000))

DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
ORDER BY number, colid
FOR READ ONLY
OPEN ms_crs_syscom

Select @HadAlter=0, @Note=0, @NoteLine=0

FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
--替換Create->Alter
If @HadAlter = 0
Begin
   Select @Start=1, @Pos = 0
   While @Start <= Len(@SysComText) -1
   begin
    If SubString(@SysComText, @Start, 2) = '/*'
     Select @Note = @Note + 1, @Start = @Start + 2, @NoteLine = 0
    Else If SubString(@SysComText, @Start, 2) = '*/'
     Select @Note = @Note - 1, @Start = @Start + 2, @NoteLine = 0
    Else if SubString(@SysComText, @Start, 2) = Char(13) + Char(10)
     Select @Start = @Start + 2, @NoteLine = 0
    Else If SubString(@SysComText, @Start, 2) = '--'
    begin
     Select @Start = @Start + 2
     If @Note = 0
      Set @NoteLine = 1
    end Else
    Begin
     If @Note = 0 And @NoteLine = 0
     Begin
      if Substring(@SysComText, @Start, 7) = Char(9) + N'Create'
       Set @Pos = @Start + 1
      Else If Substring(@SysComText, @Start, 7) = N' Create'
       Set @pos = @Start + 1
      Else If Substring(@SysComtext, @Start, 7) = N'Create '
       Set @Pos = @Start
      Else If Substring(@SysComtext, @Start, 7) = N'Create' + Char(9)
       Set @Pos = @Start
      If @Pos <> 0
       Break
     End
     Set @Start = @Start + 1
    End
   end
   --替換
   If @Pos > 0
    Select @SyscomText = STUFF(@SyscomText, @Pos, 6, 'Alter'), @HadAlter = 1
End

--放入臨時表#
Insert Into #(a) Values(@SyscomText)

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

--計算要定義多少個變量,及每行記錄屬于哪個變量
UPDATE a SET @i=id/i,g=@i
FROM # a,(SELECT i=4000/MAX(LEN(a)) FROM #)b

SET @ic=@i

--計算@Sql1/@Sql2/@Sql3/@Sql4的值
Select @Sql1=N'', @Sql2=N'', @Sql3=N'', @Sql4=N''
WHILE @ic>=0
SELECT
   @sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1,
   @sql2=N',@'+@ic+N'=N'''''+@sql2,
   @sql3=N',@'+@ic
    +N'=CASE g WHEN '+@ic
    +N' THEN @'+@ic+N'+a ELSE @'+@ic
    +N' END'+@sql3,
   @sql4=N'+@'+@ic+@sql4,
   @ic=@ic-1

--去第1個逗號
SELECT
@sql1=STUFF(@sql1,1,1,N''),
@sql2=STUFF(@sql2,1,1,N''),
@sql3=STUFF(@sql3,1,1,N''),
@sql4=STUFF(@sql4,1,1,N'')

--執行
EXEC(N'DECLARE '+@sql1+
N' SELECT ' + @sql2 +
N' SELECT ' + @sql3 + N' FROM # ' +
N' EXEC('+@sql4+ N')')