CREATE Proc [dbo].[Proc_ExportProc]
(
@Filter nvarchar(200)='' --查询条件 例如 and 1=1
)
/*
Create By HJ 2007-12-3
备份存储过程,函数,触发器等,可直接运行
2010-4-7,再次整理,当存储过程字符数超过8000时,会出现错误
注:导出存储过程,最好使用系统导出脚本功能。
*/
As
Begin
IF @Filter IS NULL
SET @Filter = ''
Declare @Text varchar(8000),
@Name nvarchar(100),
@XType nvarchar(20)
Print 'USE ' + db_name()
Print 'GO'
Declare myCurrsor Cursor For
Select a.[Text],b.[name],b.xtype
FROM syscomments a
INNER JOIN Sysobjects b ON a.id=b.id + @Filter
--WHERE (b.xtype='TR' or b.xtype='FN' or b.xtype='P') + @Filter
Open myCurrsor
Fetch Next from myCurrsor into @Text,@Name,@XType
While @@Fetch_Status = 0 --表示读取到数据,相当于 ADO 中 Not Eof
Begin
If @XType = 'TR'
Begin
Print 'IF EXISTS(SELECT 1 FROM Sysobjects WHERE xtype=''TR'' AND [name]='''+@Name+''')'
Print ' DROP TRIGGER '+@Name+''
End
Else If @XType = 'FN'
Begin
Print 'IF EXISTS(SELECT 1 FROM Sysobjects WHERE xtype=''FN'' AND [name]='''+@Name+''')'
Print ' DROP FUNCTION '+@Name+''
End
Else If @XType = 'P'
Begin
Print 'IF EXISTS(SELECT 1 FROM Sysobjects WHERE xtype=''P'' AND [name]='''+@Name+''')'
Print ' DROP PROCEDURE '+@Name+''
End
Else
GOTO NextFetch
Print 'EXECUTE('''+replace(@Text,'''','''''')+''')'
Print 'set ANSI_NULLS ON'
Print 'set QUOTED_IDENTIFIER ON'
Print 'GO'
NextFetch:
Fetch Next from myCurrsor into @Text,@Name,@XType
End
Close myCurrsor --关闭游标
Deallocate myCurrsor --删除游标
End