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

/*
刷新函數/視圖/存儲過程
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')')

--刪除臨時表
Drop Table #

--跳回之前的Label
  If @PrevLabel = 'F'
   Goto FunLab
  Else if @PrevLabel = 'P'
   Goto ProcLab