-删除数据库中所有存储过程和函数的sql语句
USE [TmpDb]
SELECT IDENTITY( INT,1,1 ) flag ,
[name] NAMES,xtype
INTO #tmp
FROM sysobjects
WHERE xtype = 'tf'
OR xtype = 'fn'
OR xtype = 'p'
DECLARE @tb VARCHAR(1000) ,
@type NVARCHAR(2),
@a INT ,
@b INT ,
@sql VARCHAR(8000)
SELECT @a = MIN(flag) ,
@b = MAX(flag)
FROM #tmp
WHILE @a <= @b
BEGIN
SELECT @tb = NAMES,@type=xtype
FROM #tmp
WHERE flag = @a
IF @type='p'
BEGIN
SET @sql = 'drop Procedure ' + @tb
END
ELSE
BEGIN
SET @sql = 'drop FUNCTION ' + @tb
END
PRINT @sql
EXEC(@sql)
SET @a = @a + 1
END
DROP TABLE #tmp
